How to count dates that are within a specific month while ignoring blank cells?

[Int3rn]

I am using the method described here: How to Countif by date/month/year and date range in Excel?, but I am open to changing the formula altogether if needed.

My specific formula is =SUMPRODUCT(1*(MONTH(Submissions!W:W)=1)) for January, =SUMPRODUCT(1*(MONTH(Submissions!W:W)=2)) for February, and so on.

These formulas work only if the range is W2:W10 or whatever range doesn't contain blank cells. How do I get the formula with W:W to ignore the blank cells and not return a #VALUE! error or count blank cells as January?

Thanks for your help!

 

[Fluff]

Hi & welcome to MrExcel.
How about

=SUMPRODUCT((MONTH(Submissions!W:W)=1)*(Submissions!W:W<>""))

BUT you should never use use entire columns in array functions. It can bring your workbook to a grinding halt.

 

[Int3rn]

Hi & welcome to MrExcel.
How about

=SUMPRODUCT((MONTH(Submissions!W:W)=1)*(Submissions!W:W<>""))

BUT you should never use use entire columns in array functions. It can bring your workbook to a grinding halt.

Thank you! This worked perfectly. I had no idea about using whole columns in array functions, thanks for the tip! I won't ever need to go past 200 entries, so I made the range W2:W201 and it works great.

 

[Fluff]

You're welcome & thanks for the feedback.