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

#### Claire2606

##### New Member
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!

### Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
You need to alter the SUMPRODUCT formula to exclude blank cells. What's the formula?

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!!!

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"))

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

Replies
5
Views
409
Replies
5
Views
1K
Replies
13
Views
1K
Replies
3
Views
2K
Replies
1
Views
380

1,219,574
Messages
6,149,084
Members
450,855
Latest member
onecodevee01

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back