Count blank cells

[JackDanIce]

Hi,

Have the following formula which counts the number of cells within a list based on a start date and end date:

=COUNTIF(Monthly!$I$3:$I$479,"<="&$B$3)-COUNTIF(Monthly!$I$3:$I4$79,"<"&$B$2)

How do I count just the blank cells?

$B$2 = Start date (user input)
$B$3 = End date (user input)
Monthly!$I$3:$I$479 = Range to evaluate user inputs against (ordered ascending)

Thanks,
Jack

 

[RoryA]

=SUMPRODUCT(--(Monthly!$I$3:$I$479<=&$B$3),--(Monthly!$I$3:$I4$79>=$B$2),--(Monthly!$L$3:$L4$79=""))

 

[JackDanIce]

Rory.. annoying, I should have worked that out, especially after you provided the first formula!

Thank you very much and enjoy rest of your day,
Jack

 

[rogerimee]

Hi JackDanIce,

First of all make it sure that your format date is correct because when I copied your example it is not all March. But regarding to your query please check this formula. If you are using 2003 version use the sumproduct but if you are using 2007 you can use countifs.

=COUNTIFS($I$3:$I$479,"<="&$B$3,$I$3:$I$479,">="&$B$2,$L$3:$L$479,"")

=SUMPRODUCT(--($I$3:$I$479<=$B$3),--($I$3:$I$479>=$B$2),--($L$3:$L$479=""))

I hope this will help you....

 

[JackDanIce]

Hi rogerimee and welcome to the board,

Date format is all being done via my macro and formatting is defined as I need it. The formula's are purely for management information reporting where I don't want to use marco's.

Excel version is 2003 so jonmo and rory's suggestions work as needed.

Thanks for your comments though,
Jack