Nov 04 2023 11:21 AM
Nov 04 2023 11:21 AM
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.
Nov 04 2023 11:47 AM - edited Nov 04 2023 11:50 AM
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.
Nov 04 2023 02:09 PM
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.
Nov 04 2023 02:36 PM
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.
Nov 04 2023 06:14 PM
Nov 04 2023 10:04 PMSolution
@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))
Nov 05 2023 06:01 AM