Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding values across a row based on a corresponding column value
This is probably simple, but I cannot figure it out. Question refers to
table below: Column A Column B Column C Column D ============================= Julie 600 200 300 Scott 100 200 400 Tom 132132 111 4000 Craig 100 1000 100 Question: I'm trying to help a colleague calculate the value across a row for Columns B-D when a value in Column A matches the criteria. For example, I want Excel to calculate the totals for Scott. The result should be 700. The formula I'm using is this: =SUMIF(A2:A5,"Scott",B2:D5). I know there's something wrong with my Sum_range, but I cannot figure it out. Any help is appreciated. Thank you. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding values across a row based on a corresponding column value
=SUMPRODUCT((A2:A5="scott")*(B2:B5+C2:C5+D2:D5))
For more details on SUMPRODUCT Bob Phillips http://www.xldynamic.com/source/xld.SUMPRODUCT.html best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Craig Deutsch" wrote in message ... This is probably simple, but I cannot figure it out. Question refers to table below: Column A Column B Column C Column D ============================= Julie 600 200 300 Scott 100 200 400 Tom 132132 111 4000 Craig 100 1000 100 Question: I'm trying to help a colleague calculate the value across a row for Columns B-D when a value in Column A matches the criteria. For example, I want Excel to calculate the totals for Scott. The result should be 700. The formula I'm using is this: =SUMIF(A2:A5,"Scott",B2:D5). I know there's something wrong with my Sum_range, but I cannot figure it out. Any help is appreciated. Thank you. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding values across a row based on a corresponding column value
One way:
=SUMPRODUCT(--(A1:A4="Scott"),B1:B4+C1:C4+D1:D4) In article , Craig Deutsch wrote: This is probably simple, but I cannot figure it out. Question refers to table below: Column A Column B Column C Column D ============================= Julie 600 200 300 Scott 100 200 400 Tom 132132 111 4000 Craig 100 1000 100 Question: I'm trying to help a colleague calculate the value across a row for Columns B-D when a value in Column A matches the criteria. For example, I want Excel to calculate the totals for Scott. The result should be 700. The formula I'm using is this: =SUMIF(A2:A5,"Scott",B2:D5). I know there's something wrong with my Sum_range, but I cannot figure it out. Any help is appreciated. Thank you. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding values across a row based on a corresponding column value
Try this:
=SUMPRODUCT((A2:A5="Scott")*B2:D5) "Craig Deutsch" wrote: This is probably simple, but I cannot figure it out. Question refers to table below: Column A Column B Column C Column D ============================= Julie 600 200 300 Scott 100 200 400 Tom 132132 111 4000 Craig 100 1000 100 Question: I'm trying to help a colleague calculate the value across a row for Columns B-D when a value in Column A matches the criteria. For example, I want Excel to calculate the totals for Scott. The result should be 700. The formula I'm using is this: =SUMIF(A2:A5,"Scott",B2:D5). I know there's something wrong with my Sum_range, but I cannot figure it out. Any help is appreciated. Thank you. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding values across a row based on a corresponding column value
If "Scott" appears only once in column A:
=SUM(INDEX(B1:D100,MATCH("Scott",A1:A100,0),0)) -- Biff Microsoft Excel MVP "Craig Deutsch" wrote in message ... This is probably simple, but I cannot figure it out. Question refers to table below: Column A Column B Column C Column D ============================= Julie 600 200 300 Scott 100 200 400 Tom 132132 111 4000 Craig 100 1000 100 Question: I'm trying to help a colleague calculate the value across a row for Columns B-D when a value in Column A matches the criteria. For example, I want Excel to calculate the totals for Scott. The result should be 700. The formula I'm using is this: =SUMIF(A2:A5,"Scott",B2:D5). I know there's something wrong with my Sum_range, but I cannot figure it out. Any help is appreciated. Thank you. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding values across a row based on a corresponding column value
Thanks to all who replied so promptly!
It looks like there are several ways to solve this problem, one of which is to use SUMPRODUCT, and the other to use SUM and INDEX functions. For now I went with SUMPRODUCT, which works effectively when the values in column A are unique. So in my example, it's necessary to ensure that the names are unique. This new SUMPRODUCT is evidently quite powerful. I didn't know it even existed. Must be new to Office 2007. "Craig Deutsch" wrote: This is probably simple, but I cannot figure it out. Question refers to table below: Column A Column B Column C Column D ============================= Julie 600 200 300 Scott 100 200 400 Tom 132132 111 4000 Craig 100 1000 100 Question: I'm trying to help a colleague calculate the value across a row for Columns B-D when a value in Column A matches the criteria. For example, I want Excel to calculate the totals for Scott. The result should be 700. The formula I'm using is this: =SUMIF(A2:A5,"Scott",B2:D5). I know there's something wrong with my Sum_range, but I cannot figure it out. Any help is appreciated. Thank you. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding values across a row based on a corresponding column value
Has been around for several versions prior to 2007.
Gord Dibben MS Excel MVP On Mon, 1 Dec 2008 14:49:01 -0800, Craig Deutsch wrote: This new SUMPRODUCT is evidently quite powerful. I didn't know it even existed. Must be new to Office 2007. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding values across a row based on a corresponding column value
Hi,
You can also do the following. In column E, use a SUM() formula Now in a blank cell, use the formula =sumif(A2:A5,"Scott",E2:E5). -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Craig Deutsch" wrote in message ... This is probably simple, but I cannot figure it out. Question refers to table below: Column A Column B Column C Column D ============================= Julie 600 200 300 Scott 100 200 400 Tom 132132 111 4000 Craig 100 1000 100 Question: I'm trying to help a colleague calculate the value across a row for Columns B-D when a value in Column A matches the criteria. For example, I want Excel to calculate the totals for Scott. The result should be 700. The formula I'm using is this: =SUMIF(A2:A5,"Scott",B2:D5). I know there's something wrong with my Sum_range, but I cannot figure it out. Any help is appreciated. Thank you. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding values across a row based on a corresponding column val
Hi,
The SUMIF won't work, first because the request is to sum 3 columns which SUMIF does not support. Cheers, Shane Devenshire "Ashish Mathur" wrote: Hi, You can also do the following. In column E, use a SUM() formula Now in a blank cell, use the formula =sumif(A2:A5,"Scott",E2:E5). -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Craig Deutsch" wrote in message ... This is probably simple, but I cannot figure it out. Question refers to table below: Column A Column B Column C Column D ============================= Julie 600 200 300 Scott 100 200 400 Tom 132132 111 4000 Craig 100 1000 100 Question: I'm trying to help a colleague calculate the value across a row for Columns B-D when a value in Column A matches the criteria. For example, I want Excel to calculate the totals for Scott. The result should be 700. The formula I'm using is this: =SUMIF(A2:A5,"Scott",B2:D5). I know there's something wrong with my Sum_range, but I cannot figure it out. Any help is appreciated. Thank you. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding values across a row based on a corresponding column val
Hi,
Yes there are many ways including =SUMPRODUCT((A1:A4=A8)*(B1:B4+C1:C4+D1:D4)) =LOOKUP(A8,A1:A4,B1:B4+C1:C4+D1:D4) =SUMPRODUCT(VLOOKUP(A8,A1:D4,{2,3,4})) =SUM(OFFSET($A$1,MATCH(A8,A1:A4,0)-1,1,,3)) (provided there is only one occurance of Scott) and this shortest I have seen =SUM(IF(A1:A4=A8,B1:D4,0)) This last requires array entry. By the way the oldest version I currently have installed is 2000, but to the best of my recollection SUMPRODUCT was around in version 5 (about 1994) at least. If these help, please click the Yes button Cheers, Shane Devenshire "Craig Deutsch" wrote: Thanks to all who replied so promptly! It looks like there are several ways to solve this problem, one of which is to use SUMPRODUCT, and the other to use SUM and INDEX functions. For now I went with SUMPRODUCT, which works effectively when the values in column A are unique. So in my example, it's necessary to ensure that the names are unique. This new SUMPRODUCT is evidently quite powerful. I didn't know it even existed. Must be new to Office 2007. "Craig Deutsch" wrote: This is probably simple, but I cannot figure it out. Question refers to table below: Column A Column B Column C Column D ============================= Julie 600 200 300 Scott 100 200 400 Tom 132132 111 4000 Craig 100 1000 100 Question: I'm trying to help a colleague calculate the value across a row for Columns B-D when a value in Column A matches the criteria. For example, I want Excel to calculate the totals for Scott. The result should be 700. The formula I'm using is this: =SUMIF(A2:A5,"Scott",B2:D5). I know there's something wrong with my Sum_range, but I cannot figure it out. Any help is appreciated. Thank you. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding multiple values in one column based on multiple values of the same value (text) in another column | Excel Discussion (Misc queries) | |||
Adding numerical values based on multiple values in another column | Excel Worksheet Functions | |||
Adding values from 1 col based on value in diff col | Excel Discussion (Misc queries) | |||
adding values based on criteria | Excel Worksheet Functions | |||
Adding Values Based on a Separate Column | Excel Discussion (Misc queries) |