Count occurrences of data

May 9, 2010 at 15:12:08
Specs: Windows XP
Okay, this should I think be fairly simple, but I would like to be use excel to look at a column of data and tell me how many times each value occurs.

So in a column of say names Fred appears 7 times and Bob appears 9 times, it would display the total.

You can use countIF, but you would have to tell it what to count each time, is there a way to highlight a whole column and to get Excel to analyse the values and tell how many times each one occurs.

See More: Count occurrences of data

Report •

May 9, 2010 at 18:41:25
Two options come to mind:

1 - Manually create an Advanced Filter list of Unique values from the long list and then drag a COUNTIF Formula down along side it.

Data...Filter...Advanced Filter...fill in the dialog box.

2 - Use VBA to do #1 for you:

Assuming your long list in Column A, this will put the Filtered List in Column B and the Counts in Column C,

Option Explicit
Sub CountWithoutLooking()
Dim lstA_Rw, lstB_Rw, nxt_Rw As Integer
'Determine Last Row in Column A
  lstA_Rw = Range("A" & Rows.Count).End(xlUp).Row
'Filter the list into Column B
  Range("A1:A" & lstA_Rw).AdvancedFilter Action:=xlFilterCopy, _
        CopyToRange:=Range("B1"), Unique:=True
'Determine Last Row in Filtered List
  lstB_Rw = Range("B" & Rows.Count).End(xlUp).Row
'Put Formulas next to Filtered List
Range("C1") = "Counts"
  With Range("C2:C" & lstB_Rw)
  .Formula = "=COUNTIF($A$2:$A$" & lstA_Rw & ",B2)"
  End With
End Sub

Report •

May 10, 2010 at 03:42:19

If your column of say names is in column C in cells C1 to C100
Put the name to find in cell A1 and
this formula in Cell B1:


The count will be shown in B1.

If you know the list of names that may appear in column C (in this example) then create a list of those names.
If the list is in column G, Cells G1 to G10, then use data validation in Cell A1, so that you can select from a list when you click on Cell A1.
Select Cell A1 and (in Excel 2003) select Data - Validation...
In the dialog box select List from the 'Allow:' drop-down and in the 'Source:' box enter the range of cells containing the list of possible names (e.g., =$G$1:$G$10).

Now A1 has a drop-down to select from and B1 will display the number of occurrences for the selected 'name'

This assumes that the 'names' in column C are just 'names' and not 'names' embedded in longer texts.


Report •

May 11, 2010 at 06:28:37
Thanks guys, very helpful :-)

Report •

Related Solutions

Ask Question