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.

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.

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.

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.

adding totals on item number match
 

=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.

adding totals on item number match
 

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.

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.