Excel Sum/Count data in a column 1

[tatonka]

I have a column of data and need to count the number of times a record apears within a range to a summary output.
Something similar to the following...
BOB
BOB
JOHN
BOB
TOM
BOB
ROY
ROY

Summary output...
Equal to 1 = 2 (i.e. JOHN and TOM)
Between 2 and 3 = 1 (i.e. ROY)
Greater than or equal to 4 = 1 (i.e. BOB)

Can anyone help?

 

[Molby]

Try using a pivot table.

 

[SkipVought]

Hi,

Take a look at COUNTIF
[tt]
=COUNTIF(A$1$:A$8$,A1)
[/tt]


Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[tatonka]

Thanks for your replies.

I was hoping I could do it without using a pivot table.

I have tried using =COUNTIF(A1:A8,A1:A8) and can count instances within the data but I want to summarise the number of instances within a number range.
i.e.
the number of times a "name" appears only once
the number of times a "name" appears between two and three times
etc.

 

[SkipVought]

Use the SUBTOTAL Wizard -- Data/Subtotal - count

Caveat: List must be sorted on Name.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[tatonka]

Thanks for your reply Skip, not sure I follow though!

 

[SkipVought]

Here is the result to using Data/Subtotal on your SORTED list of names...
[tt]
Name
BOB
BOB
BOB
BOB
BOB Count 4
JOHN
JOHN Count 1
ROY
ROY
ROY Count 2
TOM
TOM Count 1
Grand Count 8
[/tt]


Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[tatonka]

Oh, thanks but that's not really what I want.
I want to analyise the column and identify how many instances of each name there is, and then summarise that information.
e.g. JOHN and TOM only appear once, so there are 2 instances of a name that only appear once.
ROY appears twice, so there is 1 instance of a name that appears between two and three times.
etc.
The numbers in bold is the output I want to create.

 

[Zathras]

So why don't you want to use a Pivot table? It's the easiest way to get to what you want.

 

[SkipVought]

I agree with Zathras!

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[tatonka]

Thanks for all your help, I have ended up having to use a pivot table as you suggested.

 

[xlbo]

having to use a pivot table" - what's your issue with pivot tables - IMHO they are one of the best and most useful features available in excel !!

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[tatonka]

I have no issue with pivot tables and agree wholeheartedly with your comments about their usefulness. However, this solution leads me in to a world of hurt supporting one of our most computer illiterate users; a single formula would have made my life easier. Purely selfish motives, que sera.

 

[xlbo]

ahaaaaaah - fair enough - user support issues are a pain !

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[st24v6]

When I create pivots that other users will need to refresh etc I tend to favour a Macro that will do all i.e. refresh the table, resize etc.
just a thought, as I have had the same experience with less technical users myself.

PJ

"If you don't know how, ask 1st"

 

[SkipVought]

If your issue is refreshing the data, then put this code in ThisWorkbook OBJECT CODE WINDOW

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
   Application.ScreenUpdating = False
   For Each pvt In Sh.PivotTables
      pvt.PivotCache.Refresh
   Next
   Application.ScreenUpdating = True
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
   With Application
      .ScreenUpdating = False
      .EnableEvents = False
   End With
   For Each pvt In Sh.PivotTables
      pvt.PivotCache.Refresh
   Next
   With Application
      .ScreenUpdating = True
      .EnableEvents = True
   End With
End Sub

Your pivottables will be updated EITHER at the Sheet Activate event or the Sheet Change event.


Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[tatonka]

Thanks Skip.