We have the **Sales Rep**, **City**, and **Salary** columns. There are a few values that are repeated within the columns. We’ll count the number of occurrences of each value in a column in multiple ways.

### Method 1 – Using the COUNTIF Function

** Steps:**

- Use the following formula in E7:

`=COUNTIF(B7:B23,F7)`

Within the **COUNTIF function**, we inserted all the values of **Sales Rep** as ** range**. Our

**were every name, since we need to calculate the number of instances for every name. So as criteria we have inserted a name (first name in this case, gradually will check using every other name). It gave the number of occurrences for the name**

*criteria***Max**. As our data set is not a big one, you can have a quick look and find there are

**4 Max**inside that

**Sales Rep**column.

- Bring the cursor to the bottom-right corner of cell
**E7**and it’ll look like a plus**(+)**sign. It’s the**Fill Handle**tool. - Double-click on it for the rest of the values.

The formula doesn’t provide the correct values since the lookup range also moves when copying the formula.

- Replace the formula with the following:

`=COUNTIF($B$7:$B$23,F7)`

Let’s display the results solely on unique values:

- Select the whole
**Sales Rep**column (**B6:B23**). - Go to the
**Data**tab. - Select the
**Advanced**option in the**Sort & Filter**group.

- Select
**Copy to another location**. - The
**List Range**is automatically selected as we select if before. - In the
**Copy to**box, insert the cell reference where you want to paste it. In this case, we gave it as cell**F6**. - Check the box for
**Unique records only**. - Hit
**Enter**or click**OK**.

- We can see all the unique values from the column in a separate location in the
**F6:F12**range.

- Let’s use the previous
**COUNTIF**formula.

- Use the
**AutoFill**feature to copy the formula to the following cells.

- Here’s the formula for the
**Salary**column of our example.

`=COUNTIF($D$7:$D$23,F15)`

*Note:** From here on, we will extract the unique values to separate locations using the Sort & Filter option before using any formula*.

**Read More:** How to Count Duplicates in Column in Excel

### Method 2 – Utilizing SUM and EXACT Functions

** Steps:**

- Select cell
**G7**and enter the following formula.

`=--EXACT($B$7:$B$23,F7)`

We have written the **EXACT function** for **Sales Rep**. We used a double hyphen to convert **TRUE/FALSE** to **1** and **0**.

- Wrap the formula in a SUM:

`=SUM(--EXACT($B$7:$B$23,F7))`

- Since this is an array formula, you need to use
**Ctrl + Shift + Enter**instead of just**ENTER**to operate this formula. If you are using Excel 365, you apply it with**Enter**. - Use the
**Fill Handle**to copy the formula down.

Similarly, you can use the formula for the numbers as well. Here’s the formula for the Salary column:

`=SUM(--EXACT($D$7:$D$23,F16))`

**Read More: **How to Count Duplicates in Two Columns in Excel

### Method 3 – Inserting COUNT and IF Functions

** Steps:**

- Insert this formula in G7 for the
**Sales Rep**column.

`=IF($B$7:$B$23=F7,$D$7:$D$23)`

- Press
**Enter.**

- Wrap the formula in COUNT so it becomes:

`=COUNT(IF($B$7:$B$23=F7,$D$7:$D$23))`

- You can do this for the number values. Replace the fields with the appropriate ranges and criteria.

`=COUNT(IF($D$7:$D$23=F16,$D$7:$D$23))`

*Note:** Make sure your second parameter within the IF function is a number range and you are using Absolute Reference*.

**Read More:** How to Count Duplicate Values in Multiple Columns in Excel

### Method 4 – Applying SUM and IF Functions

** Steps:**

- Fo to cell
**G7**and insert the formula below.

`=SUM(IF($B$7:$B$23=F7,1,0))`

- Hit Enter.

The formula will work fine for the number values as well:

`=SUM(IF($D$7:$D$23=F16,1,0))`

**Read More:** How to Count Duplicate Rows in Excel

### Method 5 – Using a PivotTable

** Steps:**

- Select any cell inside the range. Here, we selected cell
**B4**. - Go to the
**Insert**tab. - Click on
**PivotTable**in the**Tables**group.

- The
**PivotTable from table or range**dialog box will open. - Check whether the
**Table/Range**is correct. - Select
**Exiting Worksheet**as we want to insert the**PivotTable**in the same sheet. - Provide the
**Location**. We’ll put the table in cell**F4**. - Click
**OK**.

- The pivot table will appear.

- Inside the
**PivotTable Fields**task pane, you will see the table’s column name in the**Field**section and four areas:**Filters**,**Columns**,**Rows**,**Values**. - Drag the
**Sales Rep**field into the**Rows**and**Values**area.

- It counts the occurrence number of each value within the
**Sales Rep**column.

**Read More: **Count the Order of Occurrence of Duplicates in Excel

## Counting Number of Occurrences with Multiple Criteria in Excel

We’ll show the demo for Max and John. From the dataset, we can see that there are values for **Max** in **New York**, **Los Angeles,** and **San Fransisco**. We want to count **Max** just from **New York** city.

- Go to cell
**H5**and paste the following formula into the cell.

`=COUNTIFS($B$5:$B$21,F5,$C$5:$C$21,G5)`

Here, we used the **COUNTIFS function** which is able to take multiple criteria.

- Press
**Enter**.

**Download the Practice Workbook**

## Further Readings

- How to Count Duplicates Based on Multiple Criteria in Excel
- How to Count Occurrences Per Day in Excel

**<< Go Back to Count Duplicates in Excel | Learn Excel**