adding totals on item number match

[still_learning]

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.

 

[FloMM2]

"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

 

[dp]

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]

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:C9)"
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

 

[Don Guillett]

=SUMPRODUCT((a2:a22="a")*b2:c22)

 

[FloMM2]

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]

This will SUM columns B & C for apples in col A
=SUMPRODUCT(--(a1:a11="apples")*b1:c11)