How many times does each value appear in a column

Question

I have a column with time data like this:

17:27:31
17:27:32
17:27:32
17:27:33
17:27:33
17:27:34
17:27:34
17:27:34
17:27:35
17:27:36

I want to count how many times each value repeats:

Value      Count
17:27:31   1
17:27:32   2
17:27:33   2
17:27:34   3
17:27:35   1
17:27:36   1

How can I do such a thing?

User · Answer

You can use CountIf   Put the following code in B1 and drag down the whole column   COUNTIF A A A1    It will look like this

User · Answer

The quickest way would be with a pivot table  Make sure your column of data has a header row  highlight the data and the header  from the insert ribbon select pivot table and then drag your header from the pivot table fields list to the row labels and to the values boxes

User · Answer

I second Dave s idea  I m not always fond of pivot tables  but in this case they are pretty straightforward to use   Here are my results     It was so simple to create it that I have even recorded a macro in case you need to do this with VBA   Sub Macro2       Macro2 Macro          Range  Table1   All   DATA     Select     ActiveWorkbook PivotCaches Create SourceType  xlDatabase  SourceData              Table1   Version  xlPivotTableVersion14  CreatePivotTable TableDestination              Sheet3 R3C7   TableName   PivotTable4   DefaultVersion             xlPivotTableVersion14     Sheets  Sheet3   Select     Cells 3  7  Select     With ActiveSheet PivotTables  PivotTable4   PivotFields  DATA            Orientation   xlRowField          Position   1     End With     ActiveSheet PivotTables  PivotTable4   AddDataField ActiveSheet PivotTables             PivotTable4   PivotFields  DATA     Count of DATA   xlCount End Sub

[excel] How many times does each value appear in a column?

The answer is

Examples related to excel

Tags