Help with SUMPRODUCT

[Aaron]

I'm using the formula below to calculate the values in each row. Its not
working and I'm stummped as to why. Note that there are blank cells in the
ranges listed, whether or not that makes a difference I dont know. The sheet
is formatted as follows..

=SUMPRODUCT(--(MONTH(A1:A65536=7),--(B1:B65536=C1:C65536),B1:B65536)


A B C
7-JAN 10 10
12-JAN 5 8
6-MAR 4 6
22-MAR 11 11
3-May 7 9
17-MAY 8 8

I need it to calculate only the data in columns B & C that are equal and
then total that data from only column B that matches column C while breaking
it down by month.

So in the example above the correct value returned from the formula should
be

Jan 10
Mar 11
May 8

Help in what I'm doing wrong it appreciated.

 

[Govind]

Hi,

Use

=SUMPRODUCT(--(MONTH(A2:A65536)=1),--(B2:B65536=C2:C65536),B2:B65536)
for Jan

=SUMPRODUCT(--(MONTH(A2:A65536)=3),--(B2:B65536=C2:C65536),B2:B65536)
for Mar

and so on.


regards

Govind.

 

[Dave Peterson]

You can't use the whole column.

I'd try to limit those ranges to just what you need (plus a bit more -- just in
case).

 

[Guest]

Thanks a ton... it works.

Now, I didnt think about this before, but what if I wanted to do the same
thing only break it apart by month/year

Ex:

Jan-03 = x
Feb-03 =x
Jan-05 =x
Feb-05 =x

Thanks for the help.

 

[Govind]

Hi,

Just add one more condition for year. For eg

=SUMPRODUCT(--(MONTH(A2:A65536)=1),--(YEAR(A2:A65536)=2005),--(B2:B65536=C2:C65536),B2:B65536)

for Jan 2005

Regards

Govind.