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