Empty cell formated as a "Date" is not recognized as "0", what to do?

[Brian F]

Excel 2010, Windows 8.1; I have a simple enough formula, =COUNTIFS(A24:A300,"=Requires Attention",C24:C300,"=0"). in English, If the cell in column A = "Requires Attention" and the cell in column C = 0, count that cell and include it in the total count. BUT, column C contains Dates and apparently Excel does not recognize an empty cell formatted as a Date as a "0" value cell. It considers it to be "1/0/1900" which isn't a date but apparently it isn't "0" either.

How do I get Excel to count an empty cell formatted as a date to be the equivalent of "0" so it will calculate?

 

[Marcelo Branco]

Maybe...

=COUNTIFS(A24:A300,"Requires Attention",C24:C300,"=")

Hope this helps

M.

 

[Brian F]

Nope, no joy.

 

[Marcelo Branco]

Nope, no joy.

An error? Wrong count?

Are there formulas in column C returning, in some cases, ""? If so, try
=COUNTIFS(A24:A300,"Requires Attention",C24:C300,"")

M.

 

[Brian F]

I tried all of those things; "", 0, =, etc. It turns out that in the statement =IF(A24="",0,1) or =IF(A24=0,0,1) Excel will accurately access an empty cell formatted as a date as either a "0" or empty ("") and return the correct value. In a COUNTIFS statement that references a blank cell formatted as a date it just won't calculate. No matter what the criteria, the COUNTIFS statement returns "0" i.e. there is nothing that meets the criteria. I've been trying for 3 hour and can't get any other result. I found an inelegant work around and I'm going to take it and move on. Thanks for your interest and help!

 

[Marcelo Branco]

I'm glad that you solved your problem.

Regards

M.