How can I prevent a date formatted cell from producing a January value when blank?

[Claire2606]

Hi,

I am putting together a spreadsheet that will inform me of numbers of new referrals, ongoing clients and completed dates (and other information eventually!), based on a sumproduct formula in a separate sheet to the raw data. The formula works fine for all categories for all months, except January. If in sheet one there is a blank cell in the completed date column, the January figures also count the blank cells due to excel using 00/01/1900 as the default date.

Can anyone help as Google has failed me!

Thanks in advance!

 

[RoryA]

You need to alter the SUMPRODUCT formula to exclude blank cells. What's the formula?

 

[Claire2606]

Hi RoryA,

The formula I am using is

=SUMPRODUCT(--(MONTH(Sheet1!$G$9:$G$350)=1),--(Sheet1!$D$9:$D$350="Male"),--(Sheet1!$C$9:$C$350="Year 6"))

Column G holds the end date information. The formula works for all other months except January when there is a blank cell (because the client is ongoing). It is counting the blank cells as being a closed client. The column is formatted to a date format.

Any help would be gratefully received!!!

 

[RoryA]

You can use:
=SUMPRODUCT(--(Sheet1!$G$9:$G$350<>""),--(MONTH(Sheet1!$G$9:$G$350)=1),--(Sheet1!$D$9:$D$350="Male"),--(Sheet1!$C$9:$C$350="Year 6"))

 

[Claire2606]

Oh Wow! Thank you Rory, you are a genius, that's been eluding me for days!