Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old February 21st 05, 05:39 PM
andy
 
Posts: n/a
Default how can I list items in a column with totals?

how can I list items in a column with totals?

I have a column containing a list of multiple (text) items.

I'd like to have a summary, so that I have a list of the items together with
the totals of how many times the item appeared.

I guess this must be simple, but don't know how to go about it.

Cheers

  #3   Report Post  
Old February 21st 05, 07:17 PM
Dave O
 
Posts: n/a
Default

Hi, Andy-
Here's how I do it, assuming all your text labels are in column A and
the numbers to be summed are in column B. I develop a grand total for
all items at the bottom of column B: call it cell B100. Under that I
list each item that appears in column A: call it cell A103 to A108.
The formula in cell B103 is
=SUMIF($A$1:$A$99,A103,$B$1:$B$99)
....which you can copy down through B108. In cell B109, develop a sum
of the individual totals and compare it to the total in B100 as a
checksum to make sure the list in A103:A108 is comprehensive.

  #4   Report Post  
Old February 22nd 05, 10:53 AM
andy
 
Posts: n/a
Default

A bit more detail...

I have a column with text entries, ie:

a
a
b
c
c
c
a
a
c

I'm looking for someway to display all the text entries, i.e. a,b,c, but
will also give how many times they appeared in the list, so it would look
something like
a 4
b 1
c 3

There may be several types of text in the column (i.e. a,b,c,d,e,f,g etc...)
so I'm looking for something where you don't have to type in the values.

There may also be empty cells in the list. I wouldn't want to count them.

Any ideas?



"andy" wrote:

how can I list items in a column with totals?

I have a column containing a list of multiple (text) items.

I'd like to have a summary, so that I have a list of the items together with
the totals of how many times the item appeared.

I guess this must be simple, but don't know how to go about it.

Cheers

  #5   Report Post  
Old February 22nd 05, 09:30 PM
Dave Peterson
 
Posts: n/a
Default

If your data is sorted, you could add a header row (if you don't have one) and
then:

Data|subtotal

Another option that is very powerful is: Data|Pivottable.

Add a single row of headers to your data (if you don't have them now).
select your data A1:A999 (through the bottom right corner)
Data|pivottable (actually, this menu item changes captions with versions of xl)
follow the wizard
(just click Next until you get to a dialog that has a Layout button on it)
click that Layout button
Drag the header for the column to the Row field.
drag the header for the Column field to the data field

If you don't see "Count of xxx" then double click on that one and
change it to Count

click ok and finish

Tada!

If you want to read more about the pivottable stuff, you may want to look at
some links:

Debra Dalgleish's pictures at Jon Peltier's site:
http://peltiertech.com/Excel/Pivots/pivottables.htm
And Debra's own site:
http://www.contextures.com/xlPivot01.html

John Walkenbach also has some at:
http://j-walk.com/ss/excel/files/general.htm
(look for Tony Gwynn's Hit Database)

Chip Pearson keeps Harald Staff's notes at:
http://www.cpearson.com/excel/pivots.htm

MS has some at (xl2000 and xl2002):
http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
http://office.microsoft.com/assistan...lconPT101.aspx

andy wrote:

A bit more detail...

I have a column with text entries, ie:

a
a
b
c
c
c
a
a
c

I'm looking for someway to display all the text entries, i.e. a,b,c, but
will also give how many times they appeared in the list, so it would look
something like
a 4
b 1
c 3

There may be several types of text in the column (i.e. a,b,c,d,e,f,g etc...)
so I'm looking for something where you don't have to type in the values.

There may also be empty cells in the list. I wouldn't want to count them.

Any ideas?

"andy" wrote:

how can I list items in a column with totals?

I have a column containing a list of multiple (text) items.

I'd like to have a summary, so that I have a list of the items together with
the totals of how many times the item appeared.

I guess this must be simple, but don't know how to go about it.

Cheers


--

Dave Peterson


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Indexing items from a pick list Pank Mehta Excel Discussion (Misc queries) 1 February 3rd 05 10:29 AM
How do I subtract 20% from one column in Excel and place totals i. Full Effect Landscaping Excel Discussion (Misc queries) 2 February 2nd 05 02:42 AM
Comparing/matching totals in a column to totals in a row Nicole L. Excel Worksheet Functions 3 January 27th 05 11:42 PM
How do I find out what items are in one list but not in another l. Michelle Craig Excel Discussion (Misc queries) 2 December 22nd 04 09:32 PM
Insert column entries from a master list RichLorn Excel Worksheet Functions 0 October 28th 04 08:57 PM


All times are GMT +1. The time now is 01:58 AM.

Powered by vBulletin® Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright ©2004-2020 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017