sumproduct bombs out

J

JN

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))
 
B

Bill Kuunders

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.
 
S

Sandy Mann

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
[email protected]
Replace@mailinator with @tiscali.co.uk
 
J

JN

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))
 
A

Aladin Akyurek

You can't inject OR in this type of formulas when the intent is to
calucutate an array instead of a single value.
try
=SUMPRODUCT((A1:A2="PF")*(or(B1:B2="LAN",B1:B2="PFT"))*(C1:C2))

:
Click to expand...

--

[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.
 
A

Aladin Akyurek

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).
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))



:
Click to expand...

--

[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.
 
S

Sandy Mann

JN
but if I re-typed the criteria (in this case PF),
Click to expand...

Is there a possibility that you have a space before or after the letters?

Try:

=SUMPRODUCT((trim($C$157:$C$5105)="PF")*(Trim($E$157:$E$5105)={"LAN","PFT","
PF"})*($Q$157:$Q$5105))

And see if this makes any difference

--
HTH

Sandy
[email protected]
Replace@mailinator with @tiscali.co.uk
 
J

JN

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!
 
A

Aladin Akyurek

Remember that...

($E$157:$E$5105={"LAN","PFT","PF"})

is not an efficient idiom.
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!
Click to expand...
[...]

--

[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.
 
S

Sandy Mann

Aladin,

Aladin Akyurek said:
Remember that...

($E$157:$E$5105={"LAN","PFT","PF"})

is not an efficient idiom.
Click to expand...

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
[email protected]
Replace@mailinator with @tiscali.co.uk

..
 
A

Aladin Akyurek

Your analysis looks correct to me. If you have more conditions to OR, it
pays of to switch to the IsNumer/Match idiom.

Sandy said:
Anyone?
Click to expand...

--

[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.
 
S

Sandy Mann

Thank you Aladin, I thought that you had missed my post

--
Sandy
[email protected]
Replace@mailinator with @tiscali.co.uk

Aladin Akyurek said:
Your analysis looks correct to me. If you have more conditions to OR, it
pays of to switch to the IsNumer/Match idiom.

Sandy said:
Anyone?
Click to expand...

--

[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.
Click to expand...
 
Top