Count occurrences of values

excel count excel-formula pivot-table countif
sagacious picture sagacious · Oct 31, 2012 · Viewed 21k times · Source

I have a column of text values with repeated values. I want to create a new column of unique values (no repeats) and a column with the frequency of each of those values.

What's the easiest way to do that? Efficiency isn't much of a concern as it's under 10,000 rows.

Answer

pnuts picture pnuts · Mar 11, 2013

For the sake of an answer rather than comments, copy column (say A) to B (say) and for B only Data > Data Tools – Remove Duplicates then in C2 enter: 

=COUNTIF(A:A,B2)

and copy down as required

OR (preferred option)

use a PivotTable as @Tim:

SO13151320 example

Related questions

Count non blank rows in a certain range/column Excel

I want to count empty (or non-blank) rows in a given column (or range). Example: I have a column which is spaning over 4 cells width, and each cell has either a single ''x'' or is empty. There is up to 100 …

Read More
How to count cells in a range with a value less than another cell in excel?

My Table looks like below if "team1" value is less than "general" value in that month, then it has blue color, if "team2" value is less than "general" value in that month, then it has pink color, Now I want …

Read More
C# EPPlus OpenXML count rows

With EPPlus and OpenXML does anyone know the syntax on how to count the rows? Say my worksheet is called "worksheet" int numberRows = worksheet.rows.count()? or worksheet.rows.dimension I'm certainly interested in the answer, but how to find …

Read More