Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HELP ON SUMPRODUCT()
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HELP ON SUMPRODUCT()
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HELP ON SUMPRODUCT()
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HELP ON SUMPRODUCT()
=SUMPRODUCT(('PAID 1003'!$F$4:$F$85='CHENNAI DUES'!$B44)*('PAID
1003'!$K$4:$M$85)) "Eddy Stan" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sumproduct? sumif(sumproduct)? | Excel Worksheet Functions | |||
IF / SUMPRODUCT HELP | Excel Worksheet Functions | |||
SUMPRODUCT | Excel Discussion (Misc queries) | |||
sumproduct | Excel Discussion (Misc queries) | |||
using -- with SUMPRODUCT | Excel Discussion (Misc queries) |