HELP ON SUMPRODUCT()

[Eddy Stan]

Hi

please help me

=SUMPRODUCT(--('PAID 1003'!$F$4:$F$85='CHENNAI DUES'!$B44),('PAID
1003'!$K$4:$K$85)*('PAID 1003'!$L$4:$L$85)*('PAID 1003'!$M$4:$M$85))
this the formula and i get value error
Idea is that if b44 is in range f4:f85, then i need sum of values in ranges
k4:k85. L4:L85 and M4:M85
similarly i want sum in same size but column y & aa also with KLM
i tried simply and by array formula, din work out
Thank you in advance

Eddy

 

[Bernie Deitrick]

Eddy,

=SUMPRODUCT(('PAID 1003'!$F$4:$F$85='CHENNAI DUES'!$B44)*(('PAID
1003'!$K$4:$K$85)+('PAID 1003'!$L$4:$L$85)+('PAID 1003'!$M$4:$M$85))

SUMPRODUCT is mostly used if you have multiple criteria. Since you have only one, you could also use

=SUMIF('PAID 1003'!$F$4:$F$85='CHENNAI DUES'!$B44,'PAID 1003'!$K$4:$K$85) +
SUMIF('PAID 1003'!$F$4:$F$85='CHENNAI DUES'!$B44,'PAID 1003'!$L$4:$L$85) +
SUMIF('PAID 1003'!$F$4:$F$85='CHENNAI DUES'!$B44,'PAID 1003'!$M$4:$M$85)

HTH,
Bernie
MS Excel MVP

 

[Eddy Stan]

Hi
I already worked as sumif 3 times with 3 ranges. I was looking for multiple
column range summing of same size.
Secondly the sumproduct dint work and give Ref# error.
Thank you

 

[Bernie Deitrick]

Eddy,

I forgot a last paren, but the formula works for me....

=SUMPRODUCT(('PAID 1003'!$F$4:$F$85='CHENNAI DUES'!$B44)*(('PAID 1003'!$K$4:$K$85)+('PAID
1003'!$L$4:$L$85)+('PAID 1003'!$M$4:$M$85)))

Do you have a worksheet named EXACTLY 'PAID 1003' (without the quotes)?
Do you have a worksheet named EXACTLY 'CHENNAI DUES' (without the quotes)?

Do any of the cells in K,L, and M have REF Errors? You cannot SUM when the cells have an error in
them....

HTH,
Bernie
MS Excel MVP

 

[Teethless mama]

=SUMPRODUCT(('PAID 1003'!$F$4:$F$85='CHENNAI DUES'!$B44)*('PAID
1003'!$K$4:$M$85))