HELP ON SUMPRODUCT()
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
"Eddy Stan" wrote in message
...
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" wrote:
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" wrote in message
...
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
|