Sumproduct with blank criteria

  • I have written the below sumproduct formula, which is referencing a table called UnitData. It is matching by two criteria, region and market territory, and then summing the applicable units.


    SUMPRODUCT((UnitData[Region]=IF($A12="",UnitData[Region],$A12))*(UnitData[Market Territory]=IF($B12="",UnitData[Market Territory],$B12)),UnitData[Equivalent Units])


    The value existing in A12 or B12 can either be specified, or left blank. If left blank, I want the sumproduct to sum all regions and/or all market territories. However, if I just leave blank, it tries to match a blank in the data table. So I was forced to add an IF statement that essentially points the data reference back to itself if A12 or B12 are blank.


    This formula works, but I was wondering if there is a more efficient way to structure it so I don't have to specify the data criteria pointing back to itself.

  • Re: Sumproduct with blank criteria


    Quote from DaneMark;775266

    If left blank, I want the sumproduct to sum all regions and/or all market territories.


    Quote from DaneMark;775266

    This formula works


    Based on your description, I'm a little surprised to hear you say that this formula "works".


    The reason being that, if, for example, A12 is blank, then this clause:


    (UnitData[Region]=IF($A12="",UnitData[Region],$A12))


    will resolve to:


    (UnitData[Region]=IF(TRUE,UnitData[Region],$A12))


    i.e.:


    (UnitData[Region]=UnitData[Region])

    which is self-evidently TRUE for all cells within that range, though I don't see what the intention is here, and certainly don't see how this could be interpreted as summing "all regions and/or all market territories".


    Regards

  • Re: Sumproduct with blank criteria


    For Region I have Central, East, and West as the choices. The intention was to have (UnitData[Region]=UnitData[Region]) when A12 is blank. That way it will sum all of Central, East, and West units...which it does. When you make the argument self-evidently TRUE, it sums everything (i.e. excludes nothing). But if I make a specific choice in cell A12, such as Central region, then it will only sum Central units...which it does correctly also. I was just wondering if there was a better way to write this formula. I can't think of any other options.

  • Re: Sumproduct with blank criteria


    Ah, I see.


    In that case, I think that your construction is a good one. Personally I would prefer (assuming you have Excel 2007 or later) SUMIFS here:

    =SUMIFS(UnitData[Equivalent Units],UnitData[Region],IF($A12="","*",$A12),UnitData[Market Territory],IF($B12="","*",$B12))


    Regards

  • Re: Sumproduct with blank criteria


    Unfortunately not, no; this is one of several advantages that COUNTIF(S)/SUMIF(S) have over SUMPRODUCT.


    Regards

  • Re: Sumproduct with blank criteria


    Hi Team


    I have 3 columns
    A DATES
    B DUPLICATE NAMES
    C TIME


    I AM VERY NEW TO VBA PLEASE SUGGEST ME TO SUM THE TOTAL TIME AS PER NAMES USING VBA


    Thanks,
    Ch Nagaraju

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!