SUMIFS problem with separate summation array

[Yakiv]

Hello everyone. I am newbie to this community and need help understanding what I did wrong. 

In the first screen shot, immediately below, the SUMIFS formula does not sum as I intended. When I remove the colomn separating the criteria arrays from the summation array (and adjust the SUMIFS formula accordingly) it works as expected (shown below the first image). This is a reduced "sample" of larger worksheet where I've managed to isolate the problem as much as I could.  In the large worksheet, the SUMIFS would work for some criteria and empty criteria cells but not for others. I did not isolate a consistent failure example with that. Is there a problem with nested formulas inside of the SUMIFS command or is there some other issue? As an aside all cells are formatted as text, except for the summation cells which are formatted as currency.

thanks for your consideration and help.

[#2]

Hi OliverScheurich
Yes I've noticed that it "tends" to work but if you play with it a bit, for example, add a few extra range:criteria conditions, it will stop working as shown in the first image. I wondered if this be a corrupted excel executable on my local machine. I intend to try it on a different machine in a few hours. 

[#3]

@Yakiv 

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.

[#4]

@Hans Vogelaar 

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.  

[#5]

@Yakiv 

Try this:

=SUMPRODUCT(H11:H60*IF(E7<>"",E11:E60=E7,1)*IF(F7<>"",F11:F60=F7,1)*IF(G7<>"",G11:G60=G7,1))

[#6]

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.

[#7 · Accepted solution]

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

[#8]

I like this solution very much indeed!!! When Mr Hans Vogellar made me aware that the * symbol represents a sequence of characters which will mot match with empty cells and suggested preloading the target cells with a "space" before his SUMPRODUCT suggestion approach (also good). But I really like your approach, it is a great work around and maintains the straight forwardness of the formulation. thank you (and Mr. Vogelaar) for your insights and great suggestions. I really appreciate how both of you took the time and effort to help me through this.