# 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

#1
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 •

#2
May 10, 2010 at 03:42:19
 Hi,If your column of say names is in column C in cells C1 to C100Put the name to find in cell A1 andthis formula in Cell B1: `=COUNTIF(C1:C100,A1)`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.Regards

Report •

#3
May 11, 2010 at 06:28:37