Help with SUMPRODUCT logic

[David Lipetz]

Hi,

I'm officially stumped regarding this formula. The formula works correctly
for month 2-12, but for month 1 the formula also returns results for cells
in the specified D range that are blank.

=SUMPRODUCT(--(MONTH(Reinstatements!$D$5:$D$77)=1),Reinstatements!$M$5:$M$77)

Logic: Sum the cells in the M range where the date in the D range is from a
specific month. In my data, there may be values in the M range without a
value (a blank value) in the D range. The formula above sums the M range
data when the D range is a blank. I do not want blank cells in the D range
evaluated.

What am I missing?

David

 

[T. Valko]

An empty cell evaluates as month 1. Use another array to test that the cell
is a number (has a date):

=SUMPRODUCT(--(ISNUMBER(Reinstatements!$D$5:$D$77)),--(MONTH(Reinstatements!$D$5:$D$77)=1),Reinstatements!$M$5:$M$77)

Biff

 

[Bob Phillips]

or add the year

=SUMPRODUCT(--(MONTH(Reinstatements!$D$5:$D$77)=1),--(YEAR(Reinstatements!$D$5:$D$77)=2007),Reinstatements!$M$5:$M$77)


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[David Lipetz]

Thanks. I ended up using a similar formula emailed directly by another
newgroup member:

=SUMPRODUCT(--(MONTH(Reinstatements!$D$5:$D$77)=1),--(Reinstatements!$D$5:$D$77<>""),Reinstatements!$M$5:$M$77)