Forum Discussion
Using COUNTIFS and Blank Criteria
Yes, I did try your method and it did not provide the correct result. I've attached another small spreadsheet showing the correct results (in green) and what I get (in RED) if I try using the COUNTIFS function in which one or more of the criteria reference cells contains a blank, and then copying that for multiple sets of criteria. I hope this better explains what I'm trying to accomplish. And I'll also ask that if the COUNTIFS function will not work in this situation, is there a statement using another function that could be copied to provide the correct results?
Hi John,
I'd use
=SUMPRODUCT(($B$4:$B$17=H5)*($C$4:$C$17=I5)*($D$4:$D$17=J5)*($E$4:$E$17=K5))
and attached
Thanks, that did the trick. It is interesting that the COUNTIFS function does not like criteria references to blank cells, but the SUMPRODUCT function can handle that. I'm unsure why but perhaps it is one of those "undocumented" features.
John,
It's documented but not in direct way. Here https://support.office.com/en-us/article/countifs-function-dda3dc6e-f74e-4aee-88bc-aa8c2a866842 you may find
If the criteria argument is a reference to an empty cell, the COUNTIFS function treats the empty cell as a 0 value.
Thus your criteria (0) doesn't match empty cells in the range.