Forum Discussion
COUNTIFS Non-Empty Cells
anthwny I did not understand what do you mean by counting empty cells.
| B | C | G | H | Result |
| AAA | BBB | AAA | BBB | 2 |
| AAA | BBB | 0 | ||
=COUNTIFS($C$5:$C$10002,H4,$B$5:$B$10002,G4)
When you use the formula it will evaluate both expression as AND function ie
AND( ($C$5:$C$10002,H4), ($B$5:$B$10002,G4))
returning TRUE , TRUE then only the value starts adding up.
also it does not count the empty cells that
binodranabhat Hi! Please see attached, you see when the data is empy, it adds upp all the empty cells
anthwny The COUNTIFS function does not include blank cells by default (if they are truly blank). Since your example is returning a count of all cells in the range for every row, this means that the cells are not in fact blank. They could be filled with values but have been applied with cell formatting to make them appear blank (ie: same font color as fill color); or they could contain zero-length strings (""). Based on your screenshots, however, another possibility is that columns B and C contain formulas which are returning zeros and have been formatted to not show zero values; then, if the cells in columns G and H are truly blank, COUNTIFS will read them as being 0, which will match with all of the zero results in columns B and C. If this is the case, you could try something along these lines:
=IF(OR(ISBLANK(G4:H4));0;COUNTIFS($C$4:$C$10001;H4;$B$4:$B$10001;G4))However, if columns G and H are not in fact blank, but rather contain all 0 values too, then it will still return 9998 for all rows. It's impossible to tell for sure what the exact cause of the problem is, based on screenshots only. If nothing else, hopefully this points you in the right direction.
