10 Ways to Count the Number of Occurrences in Microsoft Excel (2024)

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.", vbInformationEnd 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.