|
sumproduct bombs out
Can anyone tell me what I'm doing wrong on this example? The answer is 1
COL A COL B COL C PF LAN 0 PF PFT 1 =SUMPRODUCT((A1:A2="PF")*(B1:B2={"LAN","PFT"})*(C1 :C2)) |
The answer should be 1
Just enter some other numbers in C or codes in columns A and B and you'll see what it does. -- Greetings from New Zealand Bill K "JN" wrote in message ... Can anyone tell me what I'm doing wrong on this example? The answer is 1 COL A COL B COL C PF LAN 0 PF PFT 1 =SUMPRODUCT((A1:A2="PF")*(B1:B2={"LAN","PFT"})*(C1 :C2)) |
If I understand you right, enclose your first two comparisons in additional
brackets: =SUMPRODUCT(((A1:A2="PF")*(B1:B2={"LAN","PFT"}))*( C1:C2)) -- HTH Sandy Replace@mailinator with @tiscali.co.uk "JN" wrote in message ... Can anyone tell me what I'm doing wrong on this example? The answer is 1 COL A COL B COL C PF LAN 0 PF PFT 1 =SUMPRODUCT((A1:A2="PF")*(B1:B2={"LAN","PFT"})*(C1 :C2)) |
Your data and your formula return 1 for me.
What do you get? -- Jim "JN" wrote: Can anyone tell me what I'm doing wrong on this example? The answer is 1 COL A COL B COL C PF LAN 0 PF PFT 1 =SUMPRODUCT((A1:A2="PF")*(B1:B2={"LAN","PFT"})*(C1 :C2)) |
try
=SUMPRODUCT((A1:A2="PF")*(or(B1:B2="LAN",B1:B2="PF T"))*(C1:C2)) "JN" wrote: Can anyone tell me what I'm doing wrong on this example? The answer is 1 COL A COL B COL C PF LAN 0 PF PFT 1 =SUMPRODUCT((A1:A2="PF")*(B1:B2={"LAN","PFT"})*(C1 :C2)) |
I am getting a zero...like it doesn't recognize the cell containing the
number 1. I have used this formula earlier in the worksheet and it is working. I did run into the same issue on another part of the spreadsheet but if I re-typed the criteria (in this case PF), the formula would run correctly. The range on my spreadsheet is 157-5005 and I'm using 3 col. Here is the exact formula I am using: COL C COL E COL Q PF LAN 0 PF PFT 1 =SUMPRODUCT(($C$157:$C$5105="PF")*($E$157:$E$5105= {"LAN","PFT","PF"})*($Q$157:$Q$5105)) "JBoulton" wrote: Your data and your formula return 1 for me. What do you get? -- Jim "JN" wrote: Can anyone tell me what I'm doing wrong on this example? The answer is 1 COL A COL B COL C PF LAN 0 PF PFT 1 =SUMPRODUCT((A1:A2="PF")*(B1:B2={"LAN","PFT"})*(C1 :C2)) |
Are you sure that the original "PF" is not " PF" or "PF "
Regards Bill K "JN" wrote in message ... I am getting a zero...like it doesn't recognize the cell containing the number 1. I have used this formula earlier in the worksheet and it is working. I did run into the same issue on another part of the spreadsheet but if I re-typed the criteria (in this case PF), the formula would run correctly. The range on my spreadsheet is 157-5005 and I'm using 3 col. Here is the exact formula I am using: COL C COL E COL Q PF LAN 0 PF PFT 1 =SUMPRODUCT(($C$157:$C$5105="PF")*($E$157:$E$5105= {"LAN","PFT","PF"})*($Q$157:$Q$5105)) "JBoulton" wrote: Your data and your formula return 1 for me. What do you get? -- Jim "JN" wrote: Can anyone tell me what I'm doing wrong on this example? The answer is 1 COL A COL B COL C PF LAN 0 PF PFT 1 =SUMPRODUCT((A1:A2="PF")*(B1:B2={"LAN","PFT"})*(C1 :C2)) |
You can't inject OR in this type of formulas when the intent is to
calucutate an array instead of a single value. bj wrote: try =SUMPRODUCT((A1:A2="PF")*(or(B1:B2="LAN",B1:B2="PF T"))*(C1:C2)) "JN" wrote: Can anyone tell me what I'm doing wrong on this example? The answer is 1 COL A COL B COL C PF LAN 0 PF PFT 1 =SUMPRODUCT((A1:A2="PF")*(B1:B2={"LAN","PFT"})*( C1:C2)) -- [1] The SumProduct function should implicitly coerce the truth values to their Excel numeric equivalents. [2] The lookup functions should have an optional argument for the return value, defaulting to #N/A in its absence. |
It's more efficient to use:
=SUMPRODUCT(--($C$157:$C$5105="PF"),--ISNUMBER(MATCH($E$157:$E$5105,{"LAN","PFT","PF"},0 )),$Q$157:$Q$5105) instead of =SUMPRODUCT(($C$157:$C$5105="PF")*($E$157:$E$5105= {"LAN","PFT","PF"})*($Q$157:$Q$5105)) If you're not getting the desired figure, you have probably extraneous chars in cells with PF, LAN, etc. Try to run the TrimAll macro on the relevat ranges (The macro can be found by means of a search with Google). JN wrote: I am getting a zero...like it doesn't recognize the cell containing the number 1. I have used this formula earlier in the worksheet and it is working. I did run into the same issue on another part of the spreadsheet but if I re-typed the criteria (in this case PF), the formula would run correctly. The range on my spreadsheet is 157-5005 and I'm using 3 col. Here is the exact formula I am using: COL C COL E COL Q PF LAN 0 PF PFT 1 =SUMPRODUCT(($C$157:$C$5105="PF")*($E$157:$E$5105= {"LAN","PFT","PF"})*($Q$157:$Q$5105)) "JBoulton" wrote: Your data and your formula return 1 for me. What do you get? -- Jim "JN" wrote: Can anyone tell me what I'm doing wrong on this example? The answer is 1 COL A COL B COL C PF LAN 0 PF PFT 1 =SUMPRODUCT((A1:A2="PF")*(B1:B2={"LAN","PFT"})* (C1:C2)) -- [1] The SumProduct function should implicitly coerce the truth values to their Excel numeric equivalents. [2] The lookup functions should have an optional argument for the return value, defaulting to #N/A in its absence. |
THANKS SO MUCH!!!! That is what the prob was! I was importing from a txt
file and 1/2 of the file must have had leading spaces even though it didn't appear that way on the spreadsheet. I am so very grateful to everyone who posted to my question!!!! I can now rest easy tonight! "Sandy Mann" wrote: JN but if I re-typed the criteria (in this case PF), Is there a possibility that you have a space before or after the letters? Try: =SUMPRODUCT((trim($C$157:$C$5105)="PF")*(Trim($E$1 57:$E$5105)={"LAN","PFT"," PF"})*($Q$157:$Q$5105)) And see if this makes any difference -- HTH Sandy Replace@mailinator with @tiscali.co.uk "JN" wrote in message ... I am getting a zero...like it doesn't recognize the cell containing the number 1. I have used this formula earlier in the worksheet and it is working. I did run into the same issue on another part of the spreadsheet but if I re-typed the criteria (in this case PF), the formula would run correctly. The range on my spreadsheet is 157-5005 and I'm using 3 col. Here is the exact formula I am using: COL C COL E COL Q PF LAN 0 PF PFT 1 =SUMPRODUCT(($C$157:$C$5105="PF")*($E$157:$E$5105= {"LAN","PFT","PF"})*($Q$15 7:$Q$5105)) "JBoulton" wrote: Your data and your formula return 1 for me. What do you get? -- Jim "JN" wrote: Can anyone tell me what I'm doing wrong on this example? The answer is 1 COL A COL B COL C PF LAN 0 PF PFT 1 =SUMPRODUCT((A1:A2="PF")*(B1:B2={"LAN","PFT"})*(C1 :C2)) |
Remember that...
($E$157:$E$5105={"LAN","PFT","PF"}) is not an efficient idiom. JN wrote: THANKS SO MUCH!!!! That is what the prob was! I was importing from a txt file and 1/2 of the file must have had leading spaces even though it didn't appear that way on the spreadsheet. I am so very grateful to everyone who posted to my question!!!! I can now rest easy tonight! [...] -- [1] The SumProduct function should implicitly coerce the truth values to their Excel numeric equivalents. [2] The lookup functions should have an optional argument for the return value, defaulting to #N/A in its absence. |
Aladin,
"Aladin Akyurek" wrote in message ... Remember that... ($E$157:$E$5105={"LAN","PFT","PF"}) is not an efficient idiom. Can you verify, (or correct me if I am wrong), that the reason that you say that it is an *inefficient idiom* is because, using the OP's original data and formula: COL A COL B COL C PF LAN 0 PF PFT 1 =SUMPRODUCT((A1:A2="PF")*(B1:B2={"LAN","PFT"})*(C1 :C2)) (A1:A2="PF") evauates to TRUE;TRUE but (B1:B2={"LAN","PFT"}) evaluates to TRUE,FALSE;FALSE,TRUE so the (A1:A2="PF") has to be evaluated twice to match the (B1:B2={"LAN","PFT"}) whereas in =SUMPRODUCT((A1:A2="PF")*((B1:B2="LAN")+(B1:B2="PF T"))*(C1:C2)) (A1:A2="PF") only gets evaluated once although B1:B2 still gets evaluated twice, but then it was being evaluated twice anyway. -- Sandy Replace@mailinator with @tiscali.co.uk .. |
Anyone?
-- Sandy Replace@mailinator with @tiscali.co.uk "Sandy Mann" wrote in message ... Aladin, "Aladin Akyurek" wrote in message ... Remember that... ($E$157:$E$5105={"LAN","PFT","PF"}) is not an efficient idiom. Can you verify, (or correct me if I am wrong), that the reason that you say that it is an *inefficient idiom* is because, using the OP's original data and formula: COL A COL B COL C PF LAN 0 PF PFT 1 =SUMPRODUCT((A1:A2="PF")*(B1:B2={"LAN","PFT"})*(C1 :C2)) (A1:A2="PF") evauates to TRUE;TRUE but (B1:B2={"LAN","PFT"}) evaluates to TRUE,FALSE;FALSE,TRUE so the (A1:A2="PF") has to be evaluated twice to match the (B1:B2={"LAN","PFT"}) whereas in =SUMPRODUCT((A1:A2="PF")*((B1:B2="LAN")+(B1:B2="PF T"))*(C1:C2)) (A1:A2="PF") only gets evaluated once although B1:B2 still gets evaluated twice, but then it was being evaluated twice anyway. -- Sandy Replace@mailinator with @tiscali.co.uk . |
Your analysis looks correct to me. If you have more conditions to OR, it
pays of to switch to the IsNumer/Match idiom. Sandy Mann wrote: Anyone? -- Sandy Replace@mailinator with @tiscali.co.uk "Sandy Mann" wrote in message ... Aladin, "Aladin Akyurek" wrote in message . .. Remember that... ($E$157:$E$5105={"LAN","PFT","PF"}) is not an efficient idiom. Can you verify, (or correct me if I am wrong), that the reason that you say that it is an *inefficient idiom* is because, using the OP's original data and formula: COL A COL B COL C PF LAN 0 PF PFT 1 =SUMPRODUCT((A1:A2="PF")*(B1:B2={"LAN","PFT"})*( C1:C2)) (A1:A2="PF") evauates to TRUE;TRUE but (B1:B2={"LAN","PFT"}) evaluates to TRUE,FALSE;FALSE,TRUE so the (A1:A2="PF") has to be evaluated twice to match the (B1:B2={"LAN","PFT"}) whereas in =SUMPRODUCT((A1:A2="PF")*((B1:B2="LAN")+(B1:B2=" PFT"))*(C1:C2)) (A1:A2="PF") only gets evaluated once although B1:B2 still gets evaluated twice, but then it was being evaluated twice anyway. -- Sandy Replace@mailinator with @tiscali.co.uk . -- [1] The SumProduct function should implicitly coerce the truth values to their Excel numeric equivalents. [2] The lookup functions should have an optional argument for the return value, defaulting to #N/A in its absence. |
Thank you Aladin, I thought that you had missed my post
-- Sandy Replace@mailinator with @tiscali.co.uk "Aladin Akyurek" wrote in message ... Your analysis looks correct to me. If you have more conditions to OR, it pays of to switch to the IsNumer/Match idiom. Sandy Mann wrote: Anyone? -- Sandy Replace@mailinator with @tiscali.co.uk "Sandy Mann" wrote in message ... Aladin, "Aladin Akyurek" wrote in message . .. Remember that... ($E$157:$E$5105={"LAN","PFT","PF"}) is not an efficient idiom. Can you verify, (or correct me if I am wrong), that the reason that you say that it is an *inefficient idiom* is because, using the OP's original data and formula: COL A COL B COL C PF LAN 0 PF PFT 1 =SUMPRODUCT((A1:A2="PF")*(B1:B2={"LAN","PFT"})*( C1:C2)) (A1:A2="PF") evauates to TRUE;TRUE but (B1:B2={"LAN","PFT"}) evaluates to TRUE,FALSE;FALSE,TRUE so the (A1:A2="PF") has to be evaluated twice to match the (B1:B2={"LAN","PFT"}) whereas in =SUMPRODUCT((A1:A2="PF")*((B1:B2="LAN")+(B1:B2=" PFT"))*(C1:C2)) (A1:A2="PF") only gets evaluated once although B1:B2 still gets evaluated twice, but then it was being evaluated twice anyway. -- Sandy Replace@mailinator with @tiscali.co.uk . -- [1] The SumProduct function should implicitly coerce the truth values to their Excel numeric equivalents. [2] The lookup functions should have an optional argument for the return value, defaulting to #N/A in its absence. |
| All times are GMT +1. The time now is 07:12 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com