How to consolidate/sum a list

  • Thread starter Thread starter Larry Wallis
  • Start date Start date
L

Larry Wallis

I have a simple spreadsheet in the following format ...

Cell A1 with a heading Part Number, B1 heading Description and C1 heading
Quantity.

Under the headings is a list of 100 rows and a part number can appear more
than once.

What I would like to do is take all the unique part numbers and put them
into a separate list and then sum the totals for these part numbers.

Can anyone give me and idea how to do this please?

Many thanks.
 
Select the part numbers header included, do data>filter>advanced filter,
select unique records only and copy to another location, assume that the
original part numbers are in A1:A1000, assume that you copied the unique
list to H1:H200, now in I2 put

=SUMIF($A$2:$A$1000,H2,$C$2:$C$1000)

copy down all adjacent unique records
 
Larry,

Another way is to use the subtotals feature
Go to the Data menu and click Subtotals, follow the directions.

Regards,
Jim Cone


San Francisco, USA
 
Hi Larry

just a note on Jim's comments - it's important to SORT by part number before
using data / subtotals ...

an alternative approach is using Pivot Tables - Debra Dalgleish has some
good instructions on her website at www.contextures.com/tiptech.html

Cheers
JulieD
 
Peo Sjoblom said:
Select the part numbers header included, do data>filter>advanced filter,
select unique records only and copy to another location, assume that the
original part numbers are in A1:A1000, assume that you copied the unique
list to H1:H200, now in I2 put

=SUMIF($A$2:$A$1000,H2,$C$2:$C$1000)

copy down all adjacent unique records


--

Regards,

Peo Sjoblom


Larry Wallis said:
I have a simple spreadsheet in the following format ...

Cell A1 with a heading Part Number, B1 heading Description and C1 heading
Quantity.

Under the headings is a list of 100 rows and a part number can appear more
than once.

What I would like to do is take all the unique part numbers and put them
into a separate list and then sum the totals for these part numbers.

Can anyone give me and idea how to do this please?

Many thanks.
Click to expand...

Excellent. Thanks Peo.
Click to expand...
 
JulieD said:
Hi Larry

just a note on Jim's comments - it's important to SORT by part number before
using data / subtotals ...

an alternative approach is using Pivot Tables - Debra Dalgleish has some
good instructions on her website at www.contextures.com/tiptech.html

Cheers
JulieD
Click to expand...

And thanx to you too Jim and Julie.
 
Back
Top