sumproduct bombs out

[Guest]

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

 

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

 

[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
(e-mail address removed)
Replace@mailinator with @tiscali.co.uk

 

[Guest]

Your data and your formula return 1 for me.

What do you get?

 

[Guest]

try
=SUMPRODUCT((A1:A2="PF")*(or(B1:B2="LAN",B1:B2="PFT"))*(C1:C2))

 

[Guest]

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

 

[Bill Kuunders]

Are you sure that the original "PF" is not " PF" or "PF "

Regards
Bill K

 

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

:

--

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



:

--

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

 

[Sandy Mann]

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$157:$E$5105)={"LAN","PFT","
PF"})*($Q$157:$Q$5105))

And see if this makes any difference

--
HTH

Sandy
(e-mail address removed)
Replace@mailinator with @tiscali.co.uk

 

[Guest]

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!

 

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

[...]

--

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

 

[Sandy Mann]

Aladin,

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
(e-mail address removed)
Replace@mailinator with @tiscali.co.uk

..

 

[Sandy Mann]

Anyone?

 

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

Anyone?

--

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

 

[Sandy Mann]

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

--
Sandy
(e-mail address removed)
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.

Anyone?

--

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