# 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

Thanks for your feedback. Do you know if wildcards are possible within sumproduct? Doesn't seem to be the case.

• 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

• Re: Sumproduct with blank criteria

nchittim1160