Sometimes in the business field it is necessary to know how many times a value repeats whithin a report.

Depending on how large the report is, this could be a pretty hard, time-consuming task. There’s a formula in Excel that will save us a lot of time. We need to keep in mind that this formula will work in the cases when we know the possible values in a field so we can compare the values whithin  a cell range with the possible values in each cell.

The process goes as follows:

Step 1:

In this example, we’ll find out how many people have been born in each of the states of the U.S:

One of our spreadsheets is basically a list of states in the United States.

Step 2:

We proceed to enter the formula countif, which requires 2 parameters:

First, Range: The range of cells in which we will count the times a specific value appears, Second, Criteria: The value we want to find whithin the range of cells mentioned above.  In this example, our formula looks like this:


The $ sign indicates that these values are static, meaning the range of cells will always be the same.

Step 3:

Once we have the formula in one cell, we copy it by right clicking on the cell we wrote it in and selecting COPY.

Step 4:

We proceed to paste it into the cells we want it to be applied to, by right clicking on the range of cells and selecting PASTE SPECIAL

Once the PASTE SPECIAL window comes up, we select Formulas and click OK.

We’re now finished, and our chart will look like this: