There are times when we need to know the number of times that the same item is repeated within a list in Excel. This can be achieved very quickly and easily using the COUNTIF function.
Unlike the COUNTIF function, which only counts cells that have a numeric value, the COUNTIF function allows us to count any type of cell as long as its value meets the established condition.
For this reason, the COUNTIF function requires two arguments:
- The range that contains the cells that we are going to evaluate.
- The criteria that must be met by those cells that will be considered in the account.
How to count repetitions in Excel
To better understand how the COUNTIF function we will do an exercise. Suppose in column A of an Excel sheet I have a list of days of the week:
If I am interested in knowing how many times Sunday is repeated, I can use the COUNTIF function in the following way:
The first argument of the COUNTIF function is precisely the range of cells that contains the data that we want to count. In the second argument I have indicated the text string “Sunday” so that only those cells that contain this value will be counted.
In the following image you can see that this formula returns the value 3 which indicates that there are three cells within the range A1: A25 that contain the text string “Sunday”.
The COUNTIF function is not case-sensitive, so it would obtain the same result if the specified criteria were the text string “Sunday” in lowercase.
Count the occurrences in a column
Now that we know how to count the number of times a value is repeated within a range, let’s do a second example where we will count the occurrences of each of the values within the list.
The first step I suggest to do is to get the unique values from the list and that we can easily achieve by copying column A into column C, and keeping column C selected, we go to the Data tab> Data tools> Remove duplicates.
As a result we will obtain a list of the unique values of column A. Our second step will be to insert the COUNTIF function in cell D1 as follows:
In this case, the second argument of the COUNTIF function is a reference to cell C1 which contains the text string “Wednesday”, so the formula will return the number of cells that have that value as a result.
When copying the formula down, the first argument will remain fixed because it is an absolute reference, but our second argument will automatically adapt to do the count for each of the days.
In this way we have counted the number of occurrences for each value within the list. If you wish, you can sort the values in column C in ascending or descending order and the formulas in column D will always return the correct value.
In this example, it was relatively easy to know that the unique values of our list would be the maximum seven days a week, but if you had a list of names, surnames, cities, etc., it would be highly recommended to execute the command so that you can know the values that appear at least once within the list and for which you must create the formula.