COUNTIFS does not seem to count blank cells

I am trying to build a COUNTIF statement in Excel 2010 using a total of 4 sets of criteria. I would ideally like it to look something like this:

COUNTIFS(range1,value in a1,range2,value in b1,range3,value in c1,range4,value in d1)

This works fine if there are actual values in cells A1-D1. however, if one of those cells is blank, I don't get the number back that I would expect. (I am testing this by autofiltering using the criteria I'm setting in A1-D1). What DOES seem to be happening is that if C1 is blank, then the COUNTIFS returns a count of all rows where C1 is not blank.

Is there a way to set a default so that if there is no value in A1-D1, COUNTIFS includes everything?

Solution 1:

The DCOUNT function may work as an alternative.

You have to use a criteria range that includes its own labels, but blank criteria values mean "include all".

Note: In the pictures below, the blank cells have nothing in them, as if you clicked on the cell and pressed Delete.

Solution 2:

As you know, using Excel's filter will let you choose to show all items including blanks, show only blank items, or show any other combination of items. However, it will hide the unchosen items. If hiding the unchosen items is acceptable, the solution below may work for you.

You can count the rows chosen by a filter with the SUBTOTAL function as follows.

If any of the cells in your data are blank, setup your data with a dummy column as shown below. The dummy column has text in every row so we have a reliable way to count any row.

Use the SUBTOTAL function with function_num 103 and selecting the dummy value cells for the range. function_num 103 counts only rows chosen by the filter.¹

Click on a cell in your data area and turn on the filter. Select the entire dummy column (click on the cloumn D header), right-click and choose Hide.

Now when you change the items in the filter, the count will indicate the number the rows chosen by the filter.

1. I just noticed the Remarks at the bottom of the SUBTOTAL documentation say it always ignores rows not included in the result of a filter, so you can use either function_num 3 or 103 to count the dummy values.

Solution 3:

CountIFS() doesn't count blanks but one can omit blank cells using something like =COUNTIFS(M2:M51, "<>Nokia", M2:M51, "<>Samsung", M2:M51, "<>"), the last condition skips all blank cells from being included. I thought it is worth sharing.