Sumproduct in Excel 2003

  • Thread starter Thread starter lisay
  • Start date Start date
L

lisay

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.
 
Same as excel 2007 except you need to refer the row numbers instead of A:A
refer as A1:A100 or the max range as A1:A65535 (which is one less than the
max number of rows)

If this post helps click Yes
 
=SUMPRODUCT(IF(A2:A4="Jim",B2:B4,0),(C2:C4))
this is an array formula, pl press CTRL+SHIFT+ENTER simulatneously.

Vijay
 
lisay said:
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.
Click to expand...

How did you do this using Excel 2007?

In both Excel 2003 and 2007, you could write:

=sumproduct(--(A2:A100="Jim"),B2:100,C2:100)

Note: That is a normal formula, not an array formula. Commit with Enter as
usual, not ctrl+shift+Enter.


----- original message -----
 
Ashish Mathur said:
=SUMPRODUCT((A22:A24=A26)*(B22:B24)*(C22:C24))
A26 contains Jim
Click to expand...

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:B24,C22:C24)
instead of SUMPRODUCT(--(A22:A24="this"),--(X22:X24="that"),...).


----- original message -----
 
Hello all,

I am sorry I did not reply... I did not get a notification of your replies.
Anyhow, I will try all your suggestions to let you know how things work.

Thank you!
 
Back
Top