# 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
January.

=SUMPRODUCT(--(\$B\$2:\$B\$268=\$U277),--(MONTH(\$D\$2:\$D\$268)=8),(\$K\$2:\$K\$268))

Is there any way around this?```

Mike replied on 22-Aug-08 04:28 AM
```Hi,

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

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

I have...

CODE    ORDER DATE     COST
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:

=SUMPRODUCT(--(\$B\$2:\$B\$268=\$U277),
--(isnumber(\$d\$2:\$d\$268),
--(MONTH(\$D\$2:\$D\$268)=1),
(\$K\$2:\$K\$268))

--

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

=SUM(IF((B2:B4=A1)*(LEN(TRIM(C2:C4))>0)*(ISNUMBER(C2:C4)*(MONTH(C2:C4)=1)),D2:D4))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com```
john replied on 22-Aug-08 03:06 PM
```You could just add one more part tp your formula:
=SUMPRODUCT(--(\$B\$2:\$B\$268=\$U277),--(MONTH(\$D\$2:\$D\$268)=8),--(\$D\$2:\$d\$268<>""),(\$K\$2:\$K\$268))

--
John C```