CountIf criteria compare dates

Question

I am trying to create a CountIf function from range C2:C1700 where the criteria is 00/01/1900 or 0. The cells in my range are all formulas that retrieve a date. (0 if cell is blank, 4235 if cell is correct). I am using a formatting to display the date in yyyy-mm-dd (if that makes a difference).

Cell: C2 = [path]Sheet1!X2 - Value = 0 
Cell: C3 = [path]Sheet1!X3 - Value = 4235
Cell: C4 = [path]Sheet1!X4 - Value = 0
Cell: C5 = [path]Sheet1!X5 - Value = 0

Now in my sheet I would like to create a =COUNTIF('[path]Orders'!$C$2:$C$1700;0), but I can't seem to get the correct formula. Either 0 or #VALUE. I have also tried TEXT() and DATE() to format my criteria, but none worked.

Thank you.

Dave Sexton Dave Sexton · Accepted Answer · 2013-03-27T10:56:04

Here is the formula you need, this is an array formula so you have to finish it off by pressing Ctrl + Shift + Enter.

=SUM(IF($C$2:$C$1700=0,1,0))

To check for additional conditions such as blanks just add nested IFs like so:

=SUM(IF($C$2:$C$1700=0,1,IF($C$2:$C$1700="",1,0)))

CountIf criteria compare dates

1 Answers