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

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

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

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

Examples related to excel