How do I count the number of times a value appears in a column in Excel

Counting the number of times a specific value appears in a column in Excel is a common task that is often required when working with large amounts of data. Whether you need to track a particular item’s frequency or identify duplicates, Excel provides several built-in functions that can help you accomplish this task quickly and easily.

Here we have a dataset related to stationery Products Names, Product Types and their Price (USD). In this tutorial we will count the same Product Types using two different and really simple methods by following the steps below. Let’s have a look at the dataset first above.

Method – 1 Using COUNTIF Formula

Step – 1 Create the formula using COUNTIF function

• Select the cell where you want to type the formula
• Syntax of the formula is

=COUNTIF(range,value)

• In our case formula will be

=COUNTIF(B2:B11,”Writing Instruments”)

=COUNTIF(B2:B11,”Notepads & Paper”)

=COUNTIF(B2:B11,”Desk Accessories”) 

Step – 2 Implement the formula for other values

• In the formula change the criteria according to your needs in our case we required the following criteria.

=COUNTIF(B2:B11,”Notepads & Paper”)

=COUNTIF(B2:B11,”Desk Accessories”) 

Method – 2 Use Pivot table to count number of occurences

Step – 1 Locate Pivot Table Option in Insert Tab

• Select the cell where you want the pivot table.
• Go to the Insert tab.
• In the Tables group click on Pivot Table.

Step – 2 Create a PivotTable.

• After clicking on the PivotTable, the Create PivotTable menu dialog box appear.
• Select your dataset then click OK.

Step – 3 Select PivotTable Fields to count values

• In the PivotTable Field click on the column names you want to add. In our case, it will be Product Name and Product Type.
• Then drag Product Name to the Values section and Product Type to the Rows as shown above.
• This will automatically count the number of occurrences of each Product Type and display it in the pivot table as shown above.