Formula Help

[bobsacomano]

I have a spreadsheet that lists various order numbers on multiple rows
with different amounts for that order. I want to create a new "Order
Total" column and create a formula that adds all the amounts for each
order number that is the same. So looking at the table below, order
number 11111 will have a value of 175 (100 + 50 + 25 = 175) in the
Order Total column for each row where the order number is 11111.

I hope I'm clear. It can be difficult to put this stuff into words.
=)

Thanks
David

(new column)
Order Number Amount Order Total
11111 100 175
11111 50 175
22222 25 375
11111 25 175
33333 100 250
22222 250 375
33333 150 250
22222 100 375

 

[Guest]

You do a pivot table. Under the Data menu

layout should be

Row = Order Number column
Data = Sum of amount column

This will add up all the same Order number and give you the sum of the amount.

Hope that is clear as well! :0)

 

[Dave Peterson]

Sort your range (all the columns!) by the order number column.

Then apply Data|subtotals to that range.

This'll give you a new line with just the subtotals.

If you really want that same total on each of the lines:

=sumif($a$2:$a$99,a2,$b$2:$b$99)
and drag down.

Another alternative would be to use data|pivottable. You can do some very nice
summaries with that tool.

 

[Jay]

I have a spreadsheet that lists various order numbers on multiple rows

with different amounts for that order. I want to create a new "Order
Total" column and create a formula that adds all the amounts for each
order number that is the same. So looking at the table below, order
number 11111 will have a value of 175 (100 + 50 + 25 = 175) in the
Order Total column for each row where the order number is 11111.

I hope I'm clear. It can be difficult to put this stuff into words.
... (new column)
Order Number Amount Order Total
11111 100 175
11111 50 175
22222 25 375
11111 25 175
33333 100 250
22222 250 375
33333 150 250
22222 100 375

One way is illustrated in the csv file below.

------------------------ cut here ------------------------
Order Number,Amount,Order Total
11111,100,"=SUMIF(A:A,A2,B:B)"
11111,50,"=SUMIF(A:A,A3,B:B)"
22222,25,"=SUMIF(A:A,A4,B:B)"
11111,25,"=SUMIF(A:A,A5,B:B)"
33333,100,"=SUMIF(A:A,A6,B:B)"
22222,250,"=SUMIF(A:A,A7,B:B)"
33333,150,"=SUMIF(A:A,A8,B:B)"
22222,100,"=SUMIF(A:A,A9,B:B)"

 

[RagDyer]

Order numbers and amounts in A2 to B100.

Try this in C2:

=SUMIF($A$2:$A$100,A2,$B$2:$B$100)

And copy down to C100.
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

 

[bobsacomano]

The sumif worked. Thanks to everyone who responded! btw - in my
specific case, a subtotal or pivot table would not have worked as I
wanted each row in my table of data to have the order total.

Thanks again to everyone.

David