"Ashish Mathur" wrote:
=SUMPRODUCT((A22:A24=A26)*(B22:B24)*(C22:C24))
A26 contains Jim
Caveat emptor.... One of the problems with this form is that it results in
a #VALUE error if any of B22:B24 or C22:C24 is text, notably the null string
(""), whereas SUMPRODUCT(--(A22:A24=A6),B22:B24,C22:C24) does not.
Obviously not a problem in the OP's example. But presumably that's stripped
down.
However, I do like using multiplication to collapse conditional terms. For
example, SUMPRODUCT((A22:A24="this")*(X22:X24="that"),B22:B 24,C22:C24)
instead of SUMPRODUCT(--(A22:A24="this"),--(X22:X24="that"),...).
----- original message -----
"Ashish Mathur" wrote in message
...
=SUMPRODUCT((A22:A24=A26)*(B22:B24)*(C22:C24))
A26 contains Jim
--
Regards,
Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com
"lisay" wrote in message
...
Help please!
I have the following data:
A B C
Jim 10 50%
Jack 10 100%
Jim 5 100%
If I'm looking for the sumproduct of col B and C, if col A = Jim. I
don't
know how to do this in Excel 2003. I can do this in Excel 2007.
Any help would be much appreciated.