How to consolidate/sum a list

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

 

[Peo Sjoblom]

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

 

[Jim Cone]

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

 

[JulieD]

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

 

[Larry Wallis]

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

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.

Excellent. Thanks Peo.

 

[Larry Wallis]

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

And thanx to you too Jim and Julie.