Before you decide on which function to use, you need to determine what it
is that you want to do.

For instance, if you just wish to sum values based upon one single, simple
condition, then use SUMIF. If there are multiple conditions, or the
condition is too complex for SUMIF, then look at SUMPRODUCT, or
array-entered SUM(IF(...

The thing about all such functions is that they basically work on ranges or
arrays. If the range is independent that suggests it is being evaluated for
the values in the range. If the range is tested against another value, that
is to determine whether the condition is met or not. Unfortunately, a met
condition returns TRUE, an unmet condition returns FALSE, which is not of
much value in doing math, so it is necessary to coerce these values. That is
where the * operator comes into play, or the double unary --.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Edward" wrote in message
ps.com...
I am using SUMPRODUCT to do a table lookup/sum for me. For example I
have data below:

1 2
A color price
B red 500
C red 1000
D blue 750

If a use SUMPRODUCT((A2:A4=A2)*(B2:B4)) I get 1500 or
SUMPRODUCT((A2:A4=A4)*(B2:B4)) and I get 750 and I can use this but I
am having trouble understanding what the range argument represents. I
have tried just putting it in a cell (eg. =((A2:A4=A2)*(B2:B4)) ) but I
just get #VALUE! and if I put in just SUMPRODUCT((A2:A4=A4)) I get 0
while I would expect to get 1 or TRUE or perhaps the row number.

This seems like a powerful technique to use that I imagine has other
applications but I really do not understand the syntax.

What is going on here?

Thanks.

Edward