Formula

Brenda · May 24th, 2002, 09:12 AM

I have a 3 col. spreadsheet w/2081 lines of data. Col. A - ID #'s (have several lines with each ID#. Col B - $ amount associated with ID #. Need a formula to sum the $ amount associated with each ID# and insert total in Col C on the last line of each ID #. HELPPPPPPPPPPP PLEASE.

Seti · May 24th, 2002, 09:43 AM

Is the data sorted by ID #?

Brenda · May 24th, 2002, 09:44 AM

Example of Data:
70052 0.27
70052 0.27
70052 0.27
70052 0.48
70055 0.30
70055 0.23
70055 4.51
70055 29.34
70055 1.73
70057 15.95
70057 2.28
70058 0.94
70058 0.05
70058 0.05
70058 0.04
70059 7.08
70059 0.40
70059 13.05
70059 6.08
70059B 2.63

Seti · May 24th, 2002, 09:53 AM

I am assuming from you sample data that the ID #'s are sorted. Then the following should work:

In column C use the following code:
For Cell C1 (can be copied down for all cells)
=IF(A1<>A2,SUMIF($A$1:$A$x,A1,$B$1:$B$x),""),

where x is the end of your data. Assumes data starts in row 1. If not, change references from row 1 to the actual row that the data starts on.

Seti

Chris Davison · May 25th, 2002, 03:52 PM

If you weren't aware of the existence of data / subtotals, this would also help...

highlight your two columns then goto

data
subtotals

selecting SUM at every change in ID#

(Admittedly, it won't put the subtotals in column C like Seti's formula, but if you didn't know about this function then you may not have been aware that a seperate column is un-necessary)

either way, it's another option

May 25th, 2002, 03:52 PM	#5
Chris Davison MrExcel MVP Join Date: Feb 2002 Location: Millbank, London, UK Posts: 1,790	If you weren't aware of the existence of data / subtotals, this would also help... highlight your two columns then goto data subtotals selecting SUM at every change in ID# (Admittedly, it won't put the subtotals in column C like Seti's formula, but if you didn't know about this function then you may not have been aware that a seperate column is un-necessary) either way, it's another option __________________ :: Pharma Z - Family drugstore ::

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 24th, 2002, 09:12 AM	#1
Brenda New Member Join Date: May 2002 Location: Brenda Posts: 8	I have a 3 col. spreadsheet w/2081 lines of data. Col. A - ID #'s (have several lines with each ID#. Col B - $ amount associated with ID #. Need a formula to sum the $ amount associated with each ID# and insert total in Col C on the last line of each ID #. HELPPPPPPPPPPP PLEASE.

May 24th, 2002, 09:43 AM	#2
Seti Board Regular Join Date: May 2002 Location: CT Posts: 2,895	Is the data sorted by ID #?

May 24th, 2002, 09:44 AM	#3
Brenda New Member Join Date: May 2002 Location: Brenda Posts: 8	Example of Data: 70052 0.27 70052 0.27 70052 0.27 70052 0.48 70055 0.30 70055 0.23 70055 4.51 70055 29.34 70055 1.73 70057 15.95 70057 2.28 70058 0.94 70058 0.05 70058 0.05 70058 0.04 70059 7.08 70059 0.40 70059 13.05 70059 6.08 70059B 2.63

May 24th, 2002, 09:53 AM	#4
Seti Board Regular Join Date: May 2002 Location: CT Posts: 2,895	I am assuming from you sample data that the ID #'s are sorted. Then the following should work: In column C use the following code: For Cell C1 (can be copied down for all cells) =IF(A1<>A2,SUMIF($A$1:$A$x,A1,$B$1:$B$x),""), where x is the end of your data. Assumes data starts in row 1. If not, change references from row 1 to the actual row that the data starts on. Seti