When working with data in Excel, you often need to know how many times a value occurs in a column. It helps to analyze repeats, count the frequency of words or numbers, and clean up data.
In fact, it’s one of the ways to find duplicate cells: if a value occurs more than once, the cells are duplicates. The difference is that besides the fact of repetition, you also learn how many times each value repeats. That’s what conditional formatting can’t tell you.
Count repeats with COUNTIF
To count how many times each value repeats, use the COUNTIF function. You give it a range and a criterion, and it counts the matching cells. Put this formula in B2 and fill it down:
=COUNTIF($A$2:$A$9,A2)
It returns the number of rows that contain the value from A2. The dollar signs fix the range so it doesn’t shift as you fill the formula down. You can also refer to the whole column, =COUNTIF(A:A,A2), but on large tables it recalculates more slowly.
Here is the result on a short list. Note that COUNTIF ignores case: “apple” and “Apple” count as the same value.
| A: Fruit | B: COUNTIF | C: Occurrence number | D: Case-sensitive |
|---|---|---|---|
| apple | 3 | 1 | 2 |
| pear | 3 | 1 | 3 |
| Apple | 3 | 2 | 1 |
| plum | 1 | 1 | 1 |
| pear | 3 | 2 | 3 |
| apple | 3 | 3 | 2 |
| kiwi | 1 | 1 | 1 |
| pear | 3 | 3 | 3 |
The occurrence number
To number the repeats instead, so the first “pear” gets 1, the second 2 and so on, fix only the start of the range. The range grows as you fill the formula down:
=COUNTIF($A$2:A2,A2)
Filter by 1 and you get the list of unique values; filter by values greater than 1 and you get all repeats except the first ones.
Case-sensitive count
If “apple” and “Apple” must be counted separately, use EXACT inside SUMPRODUCT:
=SUMPRODUCT(--EXACT($A$2:$A$9,A2))
How many values repeat in total
To get one number for the whole list instead of a column, here is how many cells contain repeating values (6 in the example) and how many different values the list has (4, case-insensitive):
=SUMPRODUCT(--(COUNTIF(A2:A9,A2:A9)>1))
=SUMPRODUCT(1/COUNTIF(A2:A9,A2:A9))
And to count just one specific value, put it in the criterion: =COUNTIF(A2:A9,”pear”) returns 3. For more conditions, see the article on how to find values by several conditions.
Count repeats with a pivot table
Another handy tool is a pivot table. Put the column of values into the Rows area, then add the same column to the Values area with the Count operation. You get the list of all unique values with the number of repeats for each. In Excel 365, the same list is given by two formulas: =UNIQUE(A2:A9) in D2 and =COUNTIF(A2:A9,D2#) next to it.
Count repeats with !SEMTools
My !SEMTools add-in makes counting even easier. The Extract → Statistical metrics menu counts the number of occurrences of each element in a range in a click: select the column, and the result appears in the next column, with no COUNTIF to type, no dollar signs to remember and no formulas to fill down. The same menu has the occurrence number, the share of the total and the cumulative share.
Fast pivot
If you don’t want to add columns to the source table and just need to see quickly how many times which cells repeat, !SEMTools has a super-fast solution: a pivot table that is built on a new sheet without any setup. More in the article on how to count unique values with a fast pivot.
N-gram analysis
Sometimes you need to count not whole values but separate words inside text. That’s where n-gram analysis in !SEMTools helps: it returns the list of unique words and phrases from a column with the number of occurrences of each. Indispensable when working with keywords, SEO data and large texts.
Count repeats, occurrence numbers and shares for a whole column in a couple of clicks, without formulas.
!SEMTools solves this and hundreds of other tasks right in Excel!
Summary
I hope you found what you were looking for on this page. COUNTIF counts the repeats of each value, the growing range numbers them, SUMPRODUCT with EXACT takes case into account, and a pivot table gives the list of unique values with counts. With !SEMTools, you select the column and get the counts in a couple of clicks.
See also: