Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct in Excel 2003
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct in Excel 2003
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 --------------- Jacob Skaria "lisay" wrote: 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct in Excel 2003
=SUMPRODUCT(IF(A2:A4="Jim",B2:B4,0),(C2:C4))
this is an array formula, pl press CTRL+SHIFT+ENTER simulatneously. Vijay "lisay" wrote: 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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct in Excel 2003
"lisay" wrote:
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. 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 ----- "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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct in Excel 2003
=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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct in Excel 2003
"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. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct in Excel 2003
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! "JoeU2004" wrote: "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. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct in Excel 2003
Thank you, all. Based on your suggestions, I got it to work.
"Ashish Mathur" wrote: =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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMPRODUCT FORMULA EXCEL 2003 | Excel Worksheet Functions | |||
Countifs to Sumproduct for 2003 - was Simple Countifs.. from Fr | Excel Worksheet Functions | |||
Excel 2003 Multi Worksheet Sumproduct? | Excel Worksheet Functions | |||
Multi Worksheet Sumproduct Excel 2003 | Excel Discussion (Misc queries) | |||
XL 2003 & Sumproduct Question | Excel Discussion (Misc queries) |