COUNTIFS not counting blank cells as zero (when reference says it does)

Question

I am building a function that tests the dates listed in four (4) columns. When testing each date in each column, one of my tests measures if the date occurs previous to the date that is being used as a reference. Since the reference material says that COUNTIFS handles blank cells as -zero- values, my assumption would be that if I made a statement like:

=COUNTIFS(A1:A10,"<9/17/11")

.. that the count would equal all rows that are sooner (smaller) than "9/17/11" all the way down to zero -- which should include blank rows where dates have not been entered yet.

This is not the case, however, as the number being returned is just the number of rows that actually have a date -- and not any of the blank rows.

Before anyone suggests using a different function, I'm pretty much stuck with using COUNTIFS unless I write a novel about what I'm trying to do and how I'm trying to make it work (which isn't probably worth all the effort). My idea is working 98% so far, except for this one little part...

Answer 1

The function's explanation states;

If the criteria argument is a reference to an empty cell, the COUNTIFS function treats the empty cell as a 0 value.

I understand that to mean it counts as "0" in the count of meeting the criteria. Therefore the function is working properly.

If you need to count the blank cells along with the ones which meet the criteria, you can use the following:

=COUNTIF(A1:A10, "<9/17/2011") + COUNTBLANK(A1:A10)

This counts the the cells meeting your criteria, counts the blank cells and adds the results to give you the total.

Answer 2

Since the reference material says that COUNTIFS handles blank cells as -zero- values…

That's not what the documentation says. What it says is: (with my additions and stresses)

If the criteria argument is a reference to an empty cell, the COUNTIFS function treats the empty cell as a 0 value [as opposed to a "" value].

So, for example, in COUNTIFS(A1:A10,A11), A11 is the criteria argument and if the cell is empty the function is treated like COUNTIFS(A1:A10,0) instead of COUNTIFS(A1:A10,"").

---

You might think that for your example a solution would be:

=COUNTIFS(A1:A10,"<9/17/11",A1:A10,"")

but this doesn't work as the criteria are ANDed not ORed together.

To only use the COUNTIFS() function, you would need to do the following:

=COUNTIFS(A1:A10,"<9/17/11")+COUNTIFS(A1:A10,"")

Note that in this case the empty cells are treated as a "" value and not as a 0 value! In other words ……+COUNTIFS(A1:A10,0) doesn't work.