Excel - How to ignore blank date cells in sumproduct

Asked By Phil on 22-Aug-08 04:10 AM
I have a worksheet in which I am tring to sum the data based upon a code
range, a date range and cost range. However when I enter this formula it sums
the data from blank cells into the cell i wish to sum all the costs for


Is there any way around this?

Mike replied on 22-Aug-08 04:28 AM

I cannot make that formula misbehave, it looks fine to me can you give the
precise circumstances under which you get an error.

Phil replied on 22-Aug-08 05:19 AM
Thanks for the reponse Mike.

I have...

CS01      7-Jan-08         £200.00
MT03     14-Jan-08        £4000.00
MT03     (blank)            £500.00

This is a shortened example

The formula is fine but my problem is when a cell in the date column is
blank it assumes that it is the 00-Jan-00 and totals up the data in my
January totals Table.

I would like it to ignore the blank cells. Is this Possible?
Dave Peterson replied on 22-Aug-08 08:20 AM
Check to see if there is something numeric in that D range:



Dave Peterson
Don Guillett replied on 22-Aug-08 08:41 AM
Try this array formula


Don Guillett
Microsoft MVP Excel
SalesAid Software
john replied on 22-Aug-08 03:06 PM
You could just add one more part tp your formula:

John C