SUMPRODUCT problem

R

Ron H

I am trying to obtain a sum for rows where two conditions are true.

Col B has the part number
Col C has the customer number
Col D has payment received.

I want to obtain the sum of all payments received for a certain par
number sold to a certain customer.
In the following case Part number is 101 and customer number is 7.


I have tried the following
=SUMPRODUCT((B5:B1953=(--("101"))),(c5:c1953=(--("7"))),(d5:d1953))

I get 0 for a result which I know is not true.

I entered the following formula to prove to myself that Excel coul
find records with the 2 conidtions:
=SUMPRODUCT((B5:B1953=(--("101")))*(c5:c1953=(--("7"))))

and I got a result of 10. That is to say it can find 10 occurences o
part 101 and customer number 7.

Why is my first formula not working and providing me a sum of payment
from column D
 
B

Bob Phillips

Ron,

It seems that you are getting the things you have seen on SUMPRODUCT mixed
up. Tests, like A=10 normally resolve to TRUE or FALSE, and the -- double
unary operator is only needed if you want to coerce an array of TRUE/FALSE
values to 1/0 integers, like this

=SUMPRODUCT(--(B5:B1953=101))

SUMPRODUCT arrays are normally separated by the comma. So, if you have
multiple conditions, you can use the -- on both conditions like so

=SUMPRODUCT(--(B5:B1953=101),--(C5:C1953=7))

But, if you simply multiply two arrays of TRUE/FALSE, that implicitly
resolves to 1/0 values that are then summed, so you could use

=SUMPRODUCT((B5:B193=101)*(C5:C193=7))

Any further, final, array of values can use the same operator, or could
revert to comma.

So your formula can be written as

=SUMPRODUCT(--(B5:B1953=101),--(C5:C1953=7),(D5:D1953))

or

=SUMPRODUCT((B5:B1953=101)*(C5:C1953=7),(D5:D1953))

or

=SUMPRODUCT(--(B5:B1953=101),--(C5:C1953=7),--(D5:D1953))

or

=SUMPRODUCT((B5:B1953=101)*(C5:C1953=7)*(D5:D1953))

or even

=SUMPRODUCT(--(B5:B1953=101),--(C5:C1953=7)*(D5:D1953))



--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Top