Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

share|improve this question
    
I believe the reference entry for COUNTIFS doesn't say it counts blank cells in the source range as 0. It says (MS 2007): "If criteria is an empty cell, COUNTIFS treats it as a 0 value". –  Ellesa Sep 20 '11 at 19:16

1 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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.