Wondering how to count the number of occurrences in Excel? You’ve reached the right resource! Keep reading.

Suppose, you’ve got a tabular dataset in your worksheet where you must perform data analysis tasks like frequency analysis, error detection, duplicate analysis, and so on. In all such scenarios, you must use the skill of counting the number of occurrences for a specific number or test string in Excel.

The number of occurrences counting could involve reference values in multiple columns or simply looking up duplicate values in one column. Find below all the common methods to accomplish the tasks.

## Excel Count Number of Occurrences Using Filters

The best and quickest way to count the number of occurrences of a value in Excel is the **Filter** tool. It works best if you’re looking for duplicates, triplicates, etc., in a highly organized dataset. The target data should also contain a column header.

The **Filter** tool also allows you to count the number of occurrences based on the values of other columns of the included dataset.

To use this technique, go to your dataset, select any of the column headers in your dataset, and press `Ctrl` + `Shift` + `L` to activate the **Filter** tool.

Suppose, I want to find the occurrences of the state name **NYC** in the **City** column. So, I’d click the **Filter** icon of the **City** column and uncheck all city names except **NYC**. Click **OK** to apply the filter.

I have now narrowed down the dataset to only those rows that contain the text **NYC** in the **City** column.

I can now select the whole **City** column and figure out the total occurrences of the text **NYC** in the **City** column by referring to the **Count** value in the **Excel status bar**.

So, the text string NYC appears 4 times in the selected dataset.

Now, suppose, I want to find out how many **NYC orders** contain **30 quantities** of the merchandise ordered.

To count this occurrence, I’ll also need to apply a filter by the **value 30** in the **Qty** column of the above dataset beside the **NYC** filter in the **City** column.

The above data analysis shows that orders made from **NYC with 30 quantities** of merchandise appear **thrice** in the dataset.

## Count Number of Occurrences Using Conditional Formatting

If your input dataset is quite small and you can visually count the occurrences, you can get help from **Conditional Formatting** to highlight the cells of interest.

Select the input dataset and click on the **Conditional Formatting** button.

In the context menu that opens, click the **Highlight Cells Rules** option and choose **More Rules** from the overflow menu.

The **New Formatting Rule** dialog shall open. Here, choose **Format only cells that contain** option and configure the options as below:

- The first drop-down should be
**Cell Value** - The second drop-down should be
**equal to** - In the third field, enter the text string or value you want to format if found, such as
`AT`

in this example

Now, click the **Format** button and choose a cell background color in the **Fill** tab of the **Format Cells** dialog.

Click **OK** twice to apply the **Conditional Formatting** rule to the selected cell range on the worksheet.

Excel shall instantly highlight the target values or text strings in the cell range.

You can now visually calculate the occurrences of the selected value or text string in the dataset by referencing the background cell color. For example, the look-up value `AT`

appears **10 times** in the above database.

Alternatively, you can use the **Filter** tool and apply the **Filter by Color** option to filter out unnecessary cells from each column of the data set.

Note down the occurrence of the target value or text in each column after applying the filter and calculate the total occurrences.

## Excel Count Number of Occurrences Using Find and Replace

**Find and Replace** is another intuitive tool to count how many times a value or text string occurs in the target dataset.

Go to your worksheet that contains the dataset to be searched for the occurrences of a specific value or text string.

Press `Ctrl` + `F` to bring up the Find and Replace dialog.

In the **Find what** field, type the content you’re looking up. It’s `AT`

in this example dataset.

Now, click the **Find All** button. Excel shall create a list of all the occurrences of the keyword below the **Find and Replace** tool.

At the bottom status bar of the **Find and Replace** tool, you shall see **X cell(s) found** text. In the present tutorial, it’s **10 cell(s) found**. So, the code `AT`

appears **10 times** in the selected dataset.

## Excel Count Number of Occurrences Using COUNTIF

If you’re comfortable using Excel functions, you can start with COUNTIF to find how many times a value or text occurs in the selected dataset.

Go to your worksheet and select an empty cell. Enter the following formula inside the cell and hit `Enter`:

`=COUNTIF(C2:C9,"NYC")`

In the above formula, you must change the cell range reference `C2:C9`

and text criteria `NYC`

according to your own worksheet.

Excel shall scan through the referred cell range and count all the cells that have the text string `NYC`

.

In the current example, the occurrence value for **NYC** is `4`

.

## Excel Count Number of Occurrences Using COUNTIFS

If you’d like to calculate the count of occurrences of a specific value or text string based on multiple criteria in different columns, you need to use the **COUNTIFS** formula.

Suppose, in the above dataset, you’d like to find out the orders placed from **NYC** containing at least **30 quantities** of Macbooks.

Highlight the cell where you’d like to count the occurrence value and enter the following formula:

`=COUNTIFS(C2:C9,"NYC",B2:B9,30)`

When you’re using the formula in your own worksheet, customize the formula as mentioned below:

- The first cell range should be the range where the look-up value is available.
- If the lookup value is a number or cell reference, enter that as is. For a text string, put the look-up item within double quotes.
- The second cell range is the data range where the second criterion can be found.
`30`

is the second criterion. You must change it as well.

Hit `Enter` to calculate the occurrences of NYC with at least 30 quantities of orders.

The occurrence value is `3`

according to the **COUNTIFS** function.

You can add as many as 127 criteria in the COUNTIFS function when counting occurrences of a specific value or text string.

## Count Number of Occurrences Using FREQUENCY

Counting the number of occurrences using the **FREQUENCY** function in Excel can be useful when you want to analyze the distribution of values within a dataset.

For example, you’d like to find out how many test scores fall within the bin sizes, like `0`

to `20`

, `21`

to `30`

, `31`

to `40`

, and so on. In such datasets, you can use the **FREQUENCY** formula.

The organization of the dataset should be as outlined below:

- A column containing the test scores or any other source dataset.
- Another column containing the bin ranges as
`0`

,`20`

,`30`

, etc. - Finally, create another column named
**Occurrences**.

Select the first cell below the **Occurrences** column and enter the following formula:

`=FREQUENCY(A2:A20,B2:B11)`

Press `Enter` if you’re using Excel for the Microsoft 365 desktop app or `Ctrl` + `Shift` + `Enter` in all other Excel app editions.

Excel shall fill out the **Occurrences** column with values for each bin range.

## Count Number of Occurrences Using COUNTIF and UNIQUE

If you’d like to find out how many times all the values and texts occur in the input dataset, you can use the **UNIQUE** and **COUNTIF** functions.

For example, you want to find out all the unique cities and their order numbers in the above dataset.

Firstly, you must create a list of the unique values or text strings that appear in the dataset.

To do this, highlight a cell and enter the following formula in it:

`=UNIQUE(C2:C9)`

This is an array formula so you must press `Ctrl` + `Shift` + `Enter` to calculate in dated Excel apps. If you’re using Excel for the Microsoft 365 desktop app, you can only press `Enter`.

This shall create a list of all unique values in the referred cell range. Ensure you modify the formula according to your own dataset.

Now, enter the following formula in the empty cell to the right of the first item in the unique list you’ve just created:

`=COUNTIF(C2:C9,D2)`

Press `Enter` to calculate the occurrence value for **NYC**.

Now, drag the fill handle down the column until the last item of the list to copy the formula in all cells.

Excel shall calculate occurrences of all the items in the list.

## Excel Count Number of Occurrences Using SUM and IF

This combination formula involving **SUM** and **IF** uses a helper column to count the number of occurrences in Excel.

Firstly, create a helper column on the right side of the target column from which you’d like to count a specific value or text string.

In the first cell below the **Helper** column, enter the following formula and hit `Enter`.

`=IF(C2="NYC",1,0)`

Make sure you modify the formula when using it in your worksheet. Use the fill handle to calculate the occurrences in all the cells in the **Helper** column until reference data exists in the left side column.

Excel shall calculate the cells and input `1`

in each cell where **NYC** exists in the reference column.

Now, go to the bottom of the **Helper** column and select an empty cell. There, copy and paste the following formula:

`=SUM(D2:D9)`

Hit `Enter` to calculate the sum of the text **NYC** in the **Helper** column.

## Excel Count Number of Occurrences Using a PivotTable

You can also use a PivotTable to count occurrences in Excel.

Highlight the input dataset and click on the **PivotTable** button inside the **Tables** block of the **Insert** tab. Click **OK** on the **PivotTable from table or range** dialog to initiate the **PivotTable** console.

Drag the column header, under which the lookup value or text exists, into the **Rows and Values** cells of the **PivotTable Fields** navigation panel.

You shall see a table on the worksheet showing the count of values inside the target column.

In this table, you shall find the count of occurrences of the target value or cell. In this exercise, it’s **NYC** and it occurs **4 times** in the dataset.

## Excel Count Number of Occurrences Using VBA

You can use the following **Excel VBA** script to automatically count how many times a specific text of value appears in the dataset:

```
Sub CountOccurrences()
' Prompt the user to select the range of cells
Dim rng As Range
Set rng = Application.InputBox("Select a range", "Obtain Range Object", Type:=8)
' Prompt the user to enter the value or text string
Dim val As String
val = Application.InputBox("Enter the value or text string", "Obtain Value/String")
' Calculate occurrences
Dim cell As Range
Dim count As Integer
count = 0
For Each cell In rng
If cell.Value = val Then
count = count + 1
End If
Next cell
' Display the result
MsgBox "The value/string '" & val & "' occurs " & count & " times in the selected range.", vbInformation
End Sub
```

To create a VBA macro using this script, go through this Excel tutorial:

ðŸ“’ **Read More**: How To Use The VBA Code You Find Online

Once you execute the macro, you shall see an input box to enter the target dataset.

Then, another prompt shall show up where you must type the value or text string for which you must count the occurrences.

Finally, Excel shall show a message box showing how many times the target text or value appears in the input dataset.

## Conclusions

So far, you’ve discovered 10 different ways to count the number of occurrences in Excel.

If you need to count occurrences in a small dataset, you can try the methods that involve Excel user interface commands like **Filters**, **Conditional Formatting**, **PivotTable**, and **Find and Replace**.

If you’d like to explore various functions to accomplish this, you can use various functions like **COUNTIF**, **COUNTIFS**, **FREQUENCY**, and so on.

Finally, use the Excel VBA method, if you’d like to programmatically count occurrences. The script shall walk you through the process with input boxes so you don’t find it challenging to use a VBA macro.

## 0 Comments