It is possible to sum a column of values on the condition of other column’s values by using the SUMPRODUCT function.
This is much the way SUMIF works, but on more than one criteria column.
SUMIF has the ability to perform criteria matching with Wildcards, something that SUMPRODUCT isn’t built to do.
The following describes how to do Wildcard SUMPRODUCT.
In this example, I use three columns: Place, Season and Value.
I have four named ranges:
=COUNTA(Sheet1!$A:$A) – COUNTA(Sheet1!$A$1:$A$6)
=OFFSET(Sheet1!$A$6, 1, 0, P_Rows)
=OFFSET(Sheet1!$B$6, 1, 0, P_Rows)
=OFFSET(Sheet1!$C$6, 1, 0, P_Rows)
Just taking a sidestep for a moment to discuss the non-wildcard method:
Imagine that the Place in cell A3 is Auckland.
The formula in cell C3 could be:
=SUMPRODUCT(($A$3 = P_Place) * ($B$3 = P_Season), P_Value)
But that would not give you wildcard ability.
The formula, which was used in the screenshot, is as follows:
=SUMPRODUCT(ISNUMBER(SEARCH(C0 & $A$3 & C0, C0 & P_Place & C0)) * ($B$3 = P_Season), P_Value)
SEARCH returns a number when there is a match, #VALUE! when there is no match. ISNUMBER converts this to TRUE/FALSE.
The act of multiplying Booleans together results in 1 or 0, which in turn SUMPRODUCT multiplys with P_Value and Sums.
You might now ask “what is that C0 & $A$3 & C0 about?”
It’s used to match the whole value, not just a partial match.
Consider the effect if it were just ISNUMBER(SEARCH($A$3, P_Place)). A Place called Cromwell would also be a positive match for Well*
C0 is a named range with the formula:
Ascii character 31 is not likely to appear in your list of Places.
You could just embed CHAR(31) in the formula – I’ve chosen to use a named range just to reduce the formula length.