Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
adding totals on item number match
Hello,
I'm sure this is simple for someone: I have a list of items with counts. On the last row for a particular item, I want to add up the counts for each previous matching line and create a new 'total'. Some items have only one row, others have upto 4 with counts in each. I want one row per item with a total count in a new column, added at the end. I've searched help and I'm a little confused about the best way to approah this. |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
adding totals on item number match
"still_learning"
Have you tried, "SUMIF" ? Row Column A Column B 1 Apples 10 2 Oranges 5 3 Apples 6 4 Grapes 12 5 Apples 30 6 Pears 15 7 Oranges 5 8 Pears 15 9 Oranges 5 TOTALS: Row Collumn A Column B 14 Apples "=SUMIF(A1:B9,A14,B1:B9)" 15 Oranges "=SUMIF(A1:B9,A15,B1:B9)" 16 Grapes "=SUMIF(A1:B9,A16,B1:B9)" 17 Pears "=SUMIF(A1:B9,A17,B1:B9)" hth "still_learning" wrote: Hello, I'm sure this is simple for someone: I have a list of items with counts. On the last row for a particular item, I want to add up the counts for each previous matching line and create a new 'total'. Some items have only one row, others have upto 4 with counts in each. I want one row per item with a total count in a new column, added at the end. I've searched help and I'm a little confused about the best way to approah this. |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
adding totals on item number match
also new at this - using same example,
What if there was another set of figures in column C that I would like to also be totaled- how would the formula change? thanks dp "FloMM2" wrote: "still_learning" Have you tried, "SUMIF" ? Row Column A Column B 1 Apples 10 2 Oranges 5 3 Apples 6 4 Grapes 12 5 Apples 30 6 Pears 15 7 Oranges 5 8 Pears 15 9 Oranges 5 TOTALS: Row Collumn A Column B 14 Apples "=SUMIF(A1:B9,A14,B1:B9)" 15 Oranges "=SUMIF(A1:B9,A15,B1:B9)" 16 Grapes "=SUMIF(A1:B9,A16,B1:B9)" 17 Pears "=SUMIF(A1:B9,A17,B1:B9)" hth "still_learning" wrote: Hello, I'm sure this is simple for someone: I have a list of items with counts. On the last row for a particular item, I want to add up the counts for each previous matching line and create a new 'total'. Some items have only one row, others have upto 4 with counts in each. I want one row per item with a total count in a new column, added at the end. I've searched help and I'm a little confused about the best way to approah this. |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
adding totals on item number match
dp,
All the "SUMIF" staements would change to include the column C. If you wanted to add Column B with Column C of all the same items in Column A (ie Apples, Oranges, Grapes, and Pears) to be added together. Row Column A Column B Column C 1 Apples 10 5 2 Oranges 5 2 3 Apples 6 3 4 Grapes 12 5 5 Apples 30 10 6 Pears 15 10 7 Oranges 5 5 8 Pears 15 10 9 Oranges 5 5 Totals: Row Column A Colum B 14 Apples "=SUMIF(A1:B9,A14,B1:B9)+SUMIF(A1:C9,A14,C1:C9 )" 15 Oranges "=SUMIF(A1:B9,A15,,B1:B9)+SUMIF(A1:C9,A15,C1:C 9)" 16 Grapes "=SUMIF(A1:B9,A16,B1:B9)+SUMIF(A1:C9,A16,C1:C9 )" 17 Pears "=SUMIF(A1:B9,A17,B1:B9)+SUMIF(A1:C9,A17,C1:C9 )" If you had another column to add, just add it to the statement. hth Dennis "dp" wrote: also new at this - using same example, What if there was another set of figures in column C that I would like to also be totaled- how would the formula change? thanks dp "FloMM2" wrote: "still_learning" Have you tried, "SUMIF" ? Row Column A Column B 1 Apples 10 2 Oranges 5 3 Apples 6 4 Grapes 12 5 Apples 30 6 Pears 15 7 Oranges 5 8 Pears 15 9 Oranges 5 TOTALS: Row Collumn A Column B 14 Apples "=SUMIF(A1:B9,A14,B1:B9)" 15 Oranges "=SUMIF(A1:B9,A15,B1:B9)" 16 Grapes "=SUMIF(A1:B9,A16,B1:B9)" 17 Pears "=SUMIF(A1:B9,A17,B1:B9)" hth "still_learning" wrote: Hello, I'm sure this is simple for someone: I have a list of items with counts. On the last row for a particular item, I want to add up the counts for each previous matching line and create a new 'total'. Some items have only one row, others have upto 4 with counts in each. I want one row per item with a total count in a new column, added at the end. I've searched help and I'm a little confused about the best way to approah this. |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
adding totals on item number match
|
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
adding totals on item number match
This will SUM columns B & C for apples in col A
=SUMPRODUCT(--(a1:a11="apples")*b1:c11) -- Don Guillett Microsoft MVP Excel SalesAid Software "FloMM2" wrote in message ... Don, That does not work. The original question was to add like items in one column(A1 thru B9). In column A14 thru B17 were the totals from above. The question did not want to multiply them, just a total of like items. "Don Guillett" wrote: =SUMPRODUCT((a2:a22="a")*b2:c22) -- Don Guillett Microsoft MVP Excel SalesAid Software "dp" wrote in message ... also new at this - using same example, What if there was another set of figures in column C that I would like to also be totaled- how would the formula change? thanks dp "FloMM2" wrote: "still_learning" Have you tried, "SUMIF" ? Row Column A Column B 1 Apples 10 2 Oranges 5 3 Apples 6 4 Grapes 12 5 Apples 30 6 Pears 15 7 Oranges 5 8 Pears 15 9 Oranges 5 TOTALS: Row Collumn A Column B 14 Apples "=SUMIF(A1:B9,A14,B1:B9)" 15 Oranges "=SUMIF(A1:B9,A15,B1:B9)" 16 Grapes "=SUMIF(A1:B9,A16,B1:B9)" 17 Pears "=SUMIF(A1:B9,A17,B1:B9)" hth "still_learning" wrote: Hello, I'm sure this is simple for someone: I have a list of items with counts. On the last row for a particular item, I want to add up the counts for each previous matching line and create a new 'total'. Some items have only one row, others have upto 4 with counts in each. I want one row per item with a total count in a new column, added at the end. I've searched help and I'm a little confused about the best way to approah this. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
match item in 2 excel files | Excel Discussion (Misc queries) | |||
Calculate PivotTable Item Totals Based On Cell Contents? | Excel Worksheet Functions | |||
PivotTable Grand Totals By Item | Excel Discussion (Misc queries) | |||
does item match any in column B | Excel Discussion (Misc queries) | |||
How do I use a" item description" in excel to consolidate totals? | Excel Discussion (Misc queries) |