Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT FORMULA
I AM GETTING #VALUE! ERROR WHEN I USED BELOW FORMULA.
=SUMPRODUCT(IF(D4:D1055="M",SUMPRODUCT(--($L$6:$L$1055="New"),--($N$6:$N$1055="Cisco Systems Capital Australia P/L")),"")) PLEASE HELP. THANKS. DINESH |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT FORMULA
"Dinesh" wrote:
I AM GETTING #VALUE! ERROR WHEN I USED BELOW FORMULA. =SUMPRODUCT(IF(D4:D1055="M",SUMPRODUCT(--($L$6:$L$1055="New"),--($N$6:$N$1055="Cisco Systems Capital Australia P/L")),"")) One guess .. D4:D1055 is inconsistent with the other 2 ranges Try changing it to D6:D1055 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT FORMULA
Tried..that was just a typo..didn't work.
Thanks. Dinesh "Max" wrote: "Dinesh" wrote: I AM GETTING #VALUE! ERROR WHEN I USED BELOW FORMULA. =SUMPRODUCT(IF(D4:D1055="M",SUMPRODUCT(--($L$6:$L$1055="New"),--($N$6:$N$1055="Cisco Systems Capital Australia P/L")),"")) One guess .. D4:D1055 is inconsistent with the other 2 ranges Try changing it to D6:D1055 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT FORMULA
You have a SUMPRODUCT function which contains a single argument which is an
IF function which in turn contains a SUMPRODUCT function. IMO the error comes from the fact that the first SUMPRODUCT has only one argument which is also not an array. SUMPRODUCT requires a minimum of 2 arguments which are arrays. It seems that the first SUMPRODUCT isn't necessary. I don't know what your requirement is. Perhaps this: =IF(D4:D1055="M",SUMPRODUCT(--($L$6:$L$1055="New"),--($N$6:$N$1055="Cisco Systems Capital Australia P/L")),"") Regards, Greg "Dinesh" wrote: I AM GETTING #VALUE! ERROR WHEN I USED BELOW FORMULA. =SUMPRODUCT(IF(D4:D1055="M",SUMPRODUCT(--($L$6:$L$1055="New"),--($N$6:$N$1055="Cisco Systems Capital Australia P/L")),"")) PLEASE HELP. THANKS. DINESH |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT FORMULA
"Dinesh" wrote:
Tried..that was just a typo..didn't work. Perhaps you meant to do it as: =IF(SUMPRODUCT(--($D$6:$D$1055="M"),--($L$6:$L$1055="New"),--($N$6:$N$1055="Cisco Systems Capital Australia P/L"))=0,"",SUMPRODUCT(--($D$6:$D$1055="M"),--($L$6:$L$1055="New"),--($N$6:$N$1055="Cisco Systems Capital Australia P/L"))) If so, think it's easier/better to dispense with the error trap, i.e. use just: =SUMPRODUCT(--($D$6:$D$1055="M"),--($L$6:$L$1055="New"),--($N$6:$N$1055="Cisco Systems Capital Australia P/L")) and just suppress extraneous zeros from display in the sheet via: Tools Options View tab Uncheck "Zero values" OK -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT FORMULA
Hi!
The reason you're getting #VALUE! is because you're using an IF array. The formula will calculate if you enter it as an array (CTRL,SHIFT,ENTER). However, I'm guessing that you'll get incorrect results because the formula isn't doing what you think it's doing. It'll iterate through the IF array and for each element that is TRUE will execute the inner SUMPRODUCT. Try this: =SUMPRODUCT(--(D6:D1055="M"),--($L$6:$L$1055="New"),--($N$6:$N$1055="Cisco Systems Capital Australia P/L")) Biff "Dinesh" wrote in message ... I AM GETTING #VALUE! ERROR WHEN I USED BELOW FORMULA. =SUMPRODUCT(IF(D4:D1055="M",SUMPRODUCT(--($L$6:$L$1055="New"),--($N$6:$N$1055="Cisco Systems Capital Australia P/L")),"")) PLEASE HELP. THANKS. DINESH |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT FORMULA
Hi,
Below formula works. It doesn't give me a right answer if d6:d1055 isn't equal "M". I am formulating with <"M". I also have to add one more element to it ...IF(c6:c1055="F" or "O"... which is more complicated for me. =SUMPRODUCT(--(D6:D1055="M"),--($L$6:$L$1055="New"),--($N$6:$N$1055="Australia P/L")) Any help is greatly appreciated. Thanks. Dinesh "Biff" wrote: Hi! The reason you're getting #VALUE! is because you're using an IF array. The formula will calculate if you enter it as an array (CTRL,SHIFT,ENTER). However, I'm guessing that you'll get incorrect results because the formula isn't doing what you think it's doing. It'll iterate through the IF array and for each element that is TRUE will execute the inner SUMPRODUCT. Try this: =SUMPRODUCT(--(D6:D1055="M"),--($L$6:$L$1055="New"),--($N$6:$N$1055="Cisco Systems Capital Australia P/L")) Biff "Dinesh" wrote in message ... I AM GETTING #VALUE! ERROR WHEN I USED BELOW FORMULA. =SUMPRODUCT(IF(D4:D1055="M",SUMPRODUCT(--($L$6:$L$1055="New"),--($N$6:$N$1055="Cisco Systems Capital Australia P/L")),"")) PLEASE HELP. THANKS. DINESH |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT FORMULA
It doesn't give me a right answer if d6:d1055 isn't
equal "M". I am formulating with <"M". =SUMPRODUCT(--(D6:D1055="M") Doesn't look like you're formulating with <"M". So, you only want to count column D if it does not contain M? Try this: =SUMPRODUCT(--(ISNUMBER(MATCH(C6:C1055,{"F","O"},0))),--(D6:D1055<"M"),--($L$6:$L$1055="New"),--($N$6:$N$1055="Australia P/L")) Biff "Dinesh" wrote in message ... Hi, Below formula works. It doesn't give me a right answer if d6:d1055 isn't equal "M". I am formulating with <"M". I also have to add one more element to it ...IF(c6:c1055="F" or "O"... which is more complicated for me. =SUMPRODUCT(--(D6:D1055="M"),--($L$6:$L$1055="New"),--($N$6:$N$1055="Australia P/L")) Any help is greatly appreciated. Thanks. Dinesh "Biff" wrote: Hi! The reason you're getting #VALUE! is because you're using an IF array. The formula will calculate if you enter it as an array (CTRL,SHIFT,ENTER). However, I'm guessing that you'll get incorrect results because the formula isn't doing what you think it's doing. It'll iterate through the IF array and for each element that is TRUE will execute the inner SUMPRODUCT. Try this: =SUMPRODUCT(--(D6:D1055="M"),--($L$6:$L$1055="New"),--($N$6:$N$1055="Cisco Systems Capital Australia P/L")) Biff "Dinesh" wrote in message ... I AM GETTING #VALUE! ERROR WHEN I USED BELOW FORMULA. =SUMPRODUCT(IF(D4:D1055="M",SUMPRODUCT(--($L$6:$L$1055="New"),--($N$6:$N$1055="Cisco Systems Capital Australia P/L")),"")) PLEASE HELP. THANKS. DINESH |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT FORMULA
Hi Biff,
Here is a thing. Col C has only two values (F & O). Col D has 10 values( EXAMPLE M, F, S ETC.). Col L has two values (New & Term), Col N has 10 values(Example AU, NZ etc). I want to pick only one value from Col C (either F or O) - Col D, I want to Pick either "M" or the rest of remaining 9 values. I want pick one value from each of the Col L and Col N. Below formula gave me all under Col C. Sorry for not being clear first time. Thanks for your help. Dinesh "Biff" wrote: It doesn't give me a right answer if d6:d1055 isn't equal "M". I am formulating with <"M". =SUMPRODUCT(--(D6:D1055="M") Doesn't look like you're formulating with <"M". So, you only want to count column D if it does not contain M? Try this: =SUMPRODUCT(--(ISNUMBER(MATCH(C6:C1055,{"F","O"},0))),--(D6:D1055<"M"),--($L$6:$L$1055="New"),--($N$6:$N$1055="Australia P/L")) Biff "Dinesh" wrote in message ... Hi, Below formula works. It doesn't give me a right answer if d6:d1055 isn't equal "M". I am formulating with <"M". I also have to add one more element to it ...IF(c6:c1055="F" or "O"... which is more complicated for me. =SUMPRODUCT(--(D6:D1055="M"),--($L$6:$L$1055="New"),--($N$6:$N$1055="Australia P/L")) Any help is greatly appreciated. Thanks. Dinesh "Biff" wrote: Hi! The reason you're getting #VALUE! is because you're using an IF array. The formula will calculate if you enter it as an array (CTRL,SHIFT,ENTER). However, I'm guessing that you'll get incorrect results because the formula isn't doing what you think it's doing. It'll iterate through the IF array and for each element that is TRUE will execute the inner SUMPRODUCT. Try this: =SUMPRODUCT(--(D6:D1055="M"),--($L$6:$L$1055="New"),--($N$6:$N$1055="Cisco Systems Capital Australia P/L")) Biff "Dinesh" wrote in message ... I AM GETTING #VALUE! ERROR WHEN I USED BELOW FORMULA. =SUMPRODUCT(IF(D4:D1055="M",SUMPRODUCT(--($L$6:$L$1055="New"),--($N$6:$N$1055="Cisco Systems Capital Australia P/L")),"")) PLEASE HELP. THANKS. DINESH |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT FORMULA
Sounds like you need to use some drop down lists that list all the different
criteria then you can "mix-n-match" all you want. You'd create a drop down for each columns criteria: A1 = Drop down for column C contains F;O A2 = Drop down for column D contains M;F;S etc A3 = Drop down for column L contains New;Term A4 = Drop down for column N contains AU;NZ etc Then, you'd simply refer to the cells that hold the drop down values: =SUMPRODUCT(--(C6:C1055=A1),--(D6:D1055=A2),--(L6:L1055=A3),--(N6:N1055=A4)) Biff "Dinesh" wrote in message ... Hi Biff, Here is a thing. Col C has only two values (F & O). Col D has 10 values( EXAMPLE M, F, S ETC.). Col L has two values (New & Term), Col N has 10 values(Example AU, NZ etc). I want to pick only one value from Col C (either F or O) - Col D, I want to Pick either "M" or the rest of remaining 9 values. I want pick one value from each of the Col L and Col N. Below formula gave me all under Col C. Sorry for not being clear first time. Thanks for your help. Dinesh "Biff" wrote: It doesn't give me a right answer if d6:d1055 isn't equal "M". I am formulating with <"M". =SUMPRODUCT(--(D6:D1055="M") Doesn't look like you're formulating with <"M". So, you only want to count column D if it does not contain M? Try this: =SUMPRODUCT(--(ISNUMBER(MATCH(C6:C1055,{"F","O"},0))),--(D6:D1055<"M"),--($L$6:$L$1055="New"),--($N$6:$N$1055="Australia P/L")) Biff "Dinesh" wrote in message ... Hi, Below formula works. It doesn't give me a right answer if d6:d1055 isn't equal "M". I am formulating with <"M". I also have to add one more element to it ...IF(c6:c1055="F" or "O"... which is more complicated for me. =SUMPRODUCT(--(D6:D1055="M"),--($L$6:$L$1055="New"),--($N$6:$N$1055="Australia P/L")) Any help is greatly appreciated. Thanks. Dinesh "Biff" wrote: Hi! The reason you're getting #VALUE! is because you're using an IF array. The formula will calculate if you enter it as an array (CTRL,SHIFT,ENTER). However, I'm guessing that you'll get incorrect results because the formula isn't doing what you think it's doing. It'll iterate through the IF array and for each element that is TRUE will execute the inner SUMPRODUCT. Try this: =SUMPRODUCT(--(D6:D1055="M"),--($L$6:$L$1055="New"),--($N$6:$N$1055="Cisco Systems Capital Australia P/L")) Biff "Dinesh" wrote in message ... I AM GETTING #VALUE! ERROR WHEN I USED BELOW FORMULA. =SUMPRODUCT(IF(D4:D1055="M",SUMPRODUCT(--($L$6:$L$1055="New"),--($N$6:$N$1055="Cisco Systems Capital Australia P/L")),"")) PLEASE HELP. THANKS. DINESH |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT FORMULA
Hi Biff,
It sounds very interesting. But how do you do it. Where do you put yuor formula? Thanks to advise. Dinesh "Biff" wrote: Sounds like you need to use some drop down lists that list all the different criteria then you can "mix-n-match" all you want. You'd create a drop down for each columns criteria: A1 = Drop down for column C contains F;O A2 = Drop down for column D contains M;F;S etc A3 = Drop down for column L contains New;Term A4 = Drop down for column N contains AU;NZ etc Then, you'd simply refer to the cells that hold the drop down values: =SUMPRODUCT(--(C6:C1055=A1),--(D6:D1055=A2),--(L6:L1055=A3),--(N6:N1055=A4)) Biff "Dinesh" wrote in message ... Hi Biff, Here is a thing. Col C has only two values (F & O). Col D has 10 values( EXAMPLE M, F, S ETC.). Col L has two values (New & Term), Col N has 10 values(Example AU, NZ etc). I want to pick only one value from Col C (either F or O) - Col D, I want to Pick either "M" or the rest of remaining 9 values. I want pick one value from each of the Col L and Col N. Below formula gave me all under Col C. Sorry for not being clear first time. Thanks for your help. Dinesh "Biff" wrote: It doesn't give me a right answer if d6:d1055 isn't equal "M". I am formulating with <"M". =SUMPRODUCT(--(D6:D1055="M") Doesn't look like you're formulating with <"M". So, you only want to count column D if it does not contain M? Try this: =SUMPRODUCT(--(ISNUMBER(MATCH(C6:C1055,{"F","O"},0))),--(D6:D1055<"M"),--($L$6:$L$1055="New"),--($N$6:$N$1055="Australia P/L")) Biff "Dinesh" wrote in message ... Hi, Below formula works. It doesn't give me a right answer if d6:d1055 isn't equal "M". I am formulating with <"M". I also have to add one more element to it ...IF(c6:c1055="F" or "O"... which is more complicated for me. =SUMPRODUCT(--(D6:D1055="M"),--($L$6:$L$1055="New"),--($N$6:$N$1055="Australia P/L")) Any help is greatly appreciated. Thanks. Dinesh "Biff" wrote: Hi! The reason you're getting #VALUE! is because you're using an IF array. The formula will calculate if you enter it as an array (CTRL,SHIFT,ENTER). However, I'm guessing that you'll get incorrect results because the formula isn't doing what you think it's doing. It'll iterate through the IF array and for each element that is TRUE will execute the inner SUMPRODUCT. Try this: =SUMPRODUCT(--(D6:D1055="M"),--($L$6:$L$1055="New"),--($N$6:$N$1055="Cisco Systems Capital Australia P/L")) Biff "Dinesh" wrote in message ... I AM GETTING #VALUE! ERROR WHEN I USED BELOW FORMULA. =SUMPRODUCT(IF(D4:D1055="M",SUMPRODUCT(--($L$6:$L$1055="New"),--($N$6:$N$1055="Cisco Systems Capital Australia P/L")),"")) PLEASE HELP. THANKS. DINESH |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT FORMULA
How to create a drop down list:
http://contextures.com/xlDataVal01.html#Dropdown Biff "Dinesh" wrote in message ... Hi Biff, It sounds very interesting. But how do you do it. Where do you put yuor formula? Thanks to advise. Dinesh "Biff" wrote: Sounds like you need to use some drop down lists that list all the different criteria then you can "mix-n-match" all you want. You'd create a drop down for each columns criteria: A1 = Drop down for column C contains F;O A2 = Drop down for column D contains M;F;S etc A3 = Drop down for column L contains New;Term A4 = Drop down for column N contains AU;NZ etc Then, you'd simply refer to the cells that hold the drop down values: =SUMPRODUCT(--(C6:C1055=A1),--(D6:D1055=A2),--(L6:L1055=A3),--(N6:N1055=A4)) Biff "Dinesh" wrote in message ... Hi Biff, Here is a thing. Col C has only two values (F & O). Col D has 10 values( EXAMPLE M, F, S ETC.). Col L has two values (New & Term), Col N has 10 values(Example AU, NZ etc). I want to pick only one value from Col C (either F or O) - Col D, I want to Pick either "M" or the rest of remaining 9 values. I want pick one value from each of the Col L and Col N. Below formula gave me all under Col C. Sorry for not being clear first time. Thanks for your help. Dinesh "Biff" wrote: It doesn't give me a right answer if d6:d1055 isn't equal "M". I am formulating with <"M". =SUMPRODUCT(--(D6:D1055="M") Doesn't look like you're formulating with <"M". So, you only want to count column D if it does not contain M? Try this: =SUMPRODUCT(--(ISNUMBER(MATCH(C6:C1055,{"F","O"},0))),--(D6:D1055<"M"),--($L$6:$L$1055="New"),--($N$6:$N$1055="Australia P/L")) Biff "Dinesh" wrote in message ... Hi, Below formula works. It doesn't give me a right answer if d6:d1055 isn't equal "M". I am formulating with <"M". I also have to add one more element to it ...IF(c6:c1055="F" or "O"... which is more complicated for me. =SUMPRODUCT(--(D6:D1055="M"),--($L$6:$L$1055="New"),--($N$6:$N$1055="Australia P/L")) Any help is greatly appreciated. Thanks. Dinesh "Biff" wrote: Hi! The reason you're getting #VALUE! is because you're using an IF array. The formula will calculate if you enter it as an array (CTRL,SHIFT,ENTER). However, I'm guessing that you'll get incorrect results because the formula isn't doing what you think it's doing. It'll iterate through the IF array and for each element that is TRUE will execute the inner SUMPRODUCT. Try this: =SUMPRODUCT(--(D6:D1055="M"),--($L$6:$L$1055="New"),--($N$6:$N$1055="Cisco Systems Capital Australia P/L")) Biff "Dinesh" wrote in message ... I AM GETTING #VALUE! ERROR WHEN I USED BELOW FORMULA. =SUMPRODUCT(IF(D4:D1055="M",SUMPRODUCT(--($L$6:$L$1055="New"),--($N$6:$N$1055="Cisco Systems Capital Australia P/L")),"")) PLEASE HELP. THANKS. DINESH |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SumProduct Formula Help | Excel Worksheet Functions | |||
do I need array formula or sumproduct for counting? | Excel Worksheet Functions | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
sumproduct formula to slow | Excel Worksheet Functions | |||
adding two sumproduct formulas together | Excel Worksheet Functions |