counting number of values

  • Thread starter Thread starter Mark
  • Start date Start date
M

Mark

Hi, I am glad I have discovered this post, because I am new to excel and on a
steep learning curve due to the requirements of my work.
I have a large one-column list of values which includes duplicate values. i
would like to calculate and display the number of times each value occurrs.
for example.
1
2
4
4
6
The result should display something like 1=1, 2=1, 4=2, 6=1.
Any help would be appreciated.
Thanks. Mark =)
 
Mark,

Select your column of numbers - make sure that there is a header - and then
choose Data / Pivot table and click OK. This will create a new sheet with
a blank pivot table. Drag the button with the header word onto both the row
area and the data area, and you will get a table of the count of every
unique item in your column.

HTH,
Bernie
MS Excel MVP
 
Thanks Bernie. Sorry I should have asked if there is a way of displaying this
data "without" the use of a pivot table. Thanks anyway.
Mark.
 
Hi Mark,

Try this,

Go to Data>Filter>Advanced filter
Check copy to another location
Set your list range ($A$1:$A$100 e.g.)
Set your copy to location ( just a single cell, $B$1 e.g.)
Check unique records only and OK out

put this formula in C1 and drag down as far as is needed
(or just double click on the fill handle)
=COUNTIF(A:A,B1)

HTH
Martin
 
Mark,

<Sigh> "I would really like to drive this nail into that board _without_
using my hammer."

Learn to use your tools effectively - Excel is your tool, and Pivot Tables
are one of the most powerful features of it. Even if you are a new user,
there is no time like the present to start using Pivot Tables.

Bernie
 
Back
Top