COUNTIFS Non-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.

anthwny 

COUNTIF treats all criteria as AND. Your formula has 4 pairs of criteria range and criteria. All four must be TRUE to be counted.

In your screenshot, columns B and C seem to contain all zeroes. G5 = 0 and H5 is empty. Note that COUNTIFS treats an empty cell as zero. So, when you compare 'all zeroes' with G5, the latter is also counted as a match. Put anything except zero in G5 and/or H5 and the count on that row will become something else then 9998.

The attached demonstrates what I mean in a smaller scale.