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))
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.