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