Sumproduct (Strange Result)

[Ricky]

Hello Experts,
Do you know why my Sumproduct formula produces an incorrect result when I
overlap between December and January? It only works when overlapping months
within the same year. Why? and what's the workaround that has different
years? The month is all I need. Here's my formula in cell B1.

=SUMPRODUCT((MONTH(B1:J1)=MONTH(F1))*(B2:J2))

B1 is Dec29, 2004 to J1 which is Jan6, 2005
F1 is January (2nd, 2005)

F1 is January; this formula shouldn't be adding December's numbers located
in Row 2...but it does. Why? How do I make it to omit December's figures
and only add January's?

Thanks in advance,
Ricky

 

[Peo Sjoblom]

Try to format the dates as general, if they are dates you should get
numbers,
if you get numbers format them as mm/dd/yy (if that's what you are using in
Canada)
Also note that your formula would include blank cells since they are seen
Jan 0 1900

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

 

[duane]

i tried it and it worked for me

=SUMPRODUCT((MONTH(B1:J1)=MONTH(F1))*(B2:J2))

with b2:j2 = 1, 2, 3, etc

and result of 39

 

[Ricky]

Hi Peo & Duane,
The formats are in mmm/dd/yy. It seems that my formula works in Excel 2002
but not in Excel 2000...for dates going only from December yyyy to January
yyy+1. All other months are fine. So perplexed....

Thanks,
Ricky

 

[Don]

Ricky,

I've got 2000 and it works fine on my machine. I've tried
it both with dates formatted mm/dd/yy (my usual format)
and also with mmm/dd/yy. I tried every combination I
could think of and the formula works great.

Don

 

[Ricky]

Hi Don & Peo,
The mystery's solved. It was because I had a subtotal column in between the
range. Peo mentioned about the blank cells and the Sumproduct did, in fact,
added the subtotals on top of the intended sums.
I thought my formula would look at "months" only and ignore blank cells but
I guess not.

Thanks,
Ricky