Forum Discussion
SUMIFS problem with separate summation array
Yakiv If you want to stick with the SUMIFS() function, try using the not equal to operator with "some random text", rather than "*". The asterisk method will only include cells that contain text values of any length, excluding blank cells. The not equal to method will work with any data type (text, numbers and dates), including blank cells. Just be sure to use a random text string that is not present in the criteria range. For example, you could use "<>λ" to include all values in the range that are not equal to the Greek Small Letter Lambda symbol:
=SUMIFS(H11:H60, E11:E60, IF(E7="", "<>λ", E7), F11:F60, IF(F7="", "<>λ", F7), G11:G60, IF(G7="", "<>λ", G7))
In your sheet, G7 is empty, so the formula uses "*" as criteria for G11:G60.
"* as criteria will include all rows that are not blank in that column.
But - at least in your screenshot - the cells in G11 and down are empty, so those rows will not be included.
You might enter a space in G11:G60, or a formula ="". Both will make the cells look empty, but Excel won't treat them as empty.
i think you hit the nail on the head!!! i put a space in cells in question and the summation works as expected. thank you so much for the insight. Is there a way to tell the SUMIFS formula that the contents of the G11:G60 are a "don't care"- i.e. sum them as though there is a match? I thought the conditional statement ....IF(G7="","*",G7),....) would match the contents of any cell in that column. But per your answer, the cell needs to be occupied for that conditional statement to work.
Try this:
=SUMPRODUCT(H11:H60*IF(E7<>"",E11:E60=E7,1)*IF(F7<>"",F11:F60=F7,1)*IF(G7<>"",G11:G60=G7,1))
- yes this works. and it looks simple enough to expand should i should to put in another attr1 column. thank you so very much for your help.