10 Ways to Count the Number of Occurrences in Microsoft Excel

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.

Enabling Filter
Enabling Filter

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.

Applying NYC Filter
Applying NYC Filter

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.

Counting occurrences by filtering
Counting occurrences by filtering

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.

Highlight cells rules
Highlight cells rules

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.

New formatting rule
New formatting rule

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
Format Cells fill color
Format Cells fill color

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

Applied conditional formatting
Applied conditional formatting

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.

Filter by color
Filter by color

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.

Count of highlighted cells
Count of highlighted cells

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.

Find and replace
Find and replace

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.

Find All
Find All

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.

Using COUNTIF
Using COUNTIF

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.

Count Number of Occurrences with COUNTIF
Count Number of Occurrences with COUNTIF

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.

Example dataset for COUNTIFS
Example dataset for COUNTIFS

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

COUNTIFS function
COUNTIFS function

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.
Occurrences of NYC using COUNTIFS
Occurrences of NYC using COUNTIFS

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.

The example dataset for FREQUENCY function
The example dataset for FREQUENCY function

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.
Creating FREQUENCY formula
Creating FREQUENCY formula

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.

Find out occurrences using FREQUENCY
Find out occurrences using FREQUENCY

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.

Sample dataset
Sample dataset

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.

Using the UNIQUE formula
Using the UNIQUE formula

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.

Created a list of unique entries
Created a list of unique entries

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

Using COUNTIF to calculate occurrence
Using COUNTIF to calculate occurrence

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.

Using fill handle to copy formula
Using fill handle to copy formula

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.

Creating Helper column
Creating Helper column

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.

Entering IF formula
Entering IF formula

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

=IF(C2="NYC",1,0)
Using fill handle to copy function
Using fill handle to copy function

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.

Calculated occurrences by row for NYC
Calculated occurrences by row for NYC

Excel shall calculate the cells and input 1 in each cell where NYC exists in the reference column.

Using SUM
Using SUM

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

=SUM(D2:D9)
Count occurrences using SUM and IF
Count occurrences using SUM and IF

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.

PivotTable from table or range
PivotTable from table or range

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 column to Rows and Values
Drag the column to Rows and Values

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

Count of values in Pivot Table
Count of values in Pivot Table

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

Counting occurrences using PivotTable
Counting occurrences using PivotTable

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:

The VBA script for occurrence count
The VBA script for occurence count
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

Obtain range object
Obtain range object

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

Obtain Value or String
Obtain Value or String

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

Excel dialog box for count of occurrences
Excel dialog box for the count of 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.

About the Author

Tamal Das

Tamal Das

I'm a freelance writer at HowToExcel.org. After completing my MS in Science, I joined reputed IT consultancy companies to acquire hands-on knowledge of data analysis and data visualization techniques as a business analyst. Now, I'm a professional freelance content writer for everything Excel and its advanced support tools, like Power Pivot, Power Query, Office Scripts, and Excel VBA. I published many tutorials and how-to articles on Excel for sites like MakeUseOf, AddictiveTips, OnSheets, Technipages, and AppleToolBox. In weekends, I perform in-depth web search to learn the latest tricks and tips of Excel so I can write on these in the weekdays!

Subscribe

Subscribe for awesome Microsoft Excel videos 😃

Related Posts

Comments

0 Comments

Submit a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Get the Latest Microsoft Excel Tips

Follow Us

Follow us to stay up to date with the latest in Microsoft Excel!

Subscribe for awesome Microsoft Excel videos 😃