In Excel, you can count data in one column depending on criteria in another column or columns. For example, you need to know how many things were sold on a specific date. To do it, you must count from one column when it fulfills the requirements of another column. So, we will demonstrate five simple methods to count one particular column if another column meets the criteria in Excel.

**📁 Download Excel File**

Download the Excel file used for the demonstration from the link below.

## Learn to Count One Column If Another Column Meets Criteria in Excel with These 5 Methods

Firstly, let us get acquainted with our dataset used throughout the article as an example. Here, we have the name of the salesman, the products they sold, and the date of selling the product. Now, we want to count the sales of a specific product on a particular date or by the salesman’s name. Hence, we will use this dataset to demonstrate five simple methods to count one single column if another column meets the particular criteria in Excel.

**Approach 1**

**1. Applying Basic Functions**

First, we will use the basic functions of Excel, **COUNTIF, **and** COUNTIFS** to count one particular column if another meets the criteria.

**Basic Functions**

#### 1.1 Use of COUNTIF Function

We will apply **COUNTIF function** to count one specific column if another meets the criteria. **COUNTIF** counts the number of cells that meets a specific criterion. For instance, we will count the total number of laptops sold. Let us see the steps now.

**⬇️⬇️ STEPS ⬇️⬇️**

- At first, select cell
**C15**and insert the formula.

**=COUNTIF(C6:C12,C14)**

Here, range **C6:C12 **contains the sold product name, and **C14 **is the criteria to count.

**Basic Functions**

#### 1.2 Use of COUNTIFS Function

Now, we will use **COUNTIFS function** to count one individual column if another one meets the criteria. **COUNTIFS** counts the number of cells that meets multiple criteria. For instance, we will count the total number of laptops sold on 12/1/2022.

Let us see the steps now.

**⬇️⬇️ STEPS ⬇️⬇️**

- First, insert the following formula in cell
**C16**and have the total number of laptops sold on 12/1/2022.

**=COUNTIFS(C6:C12,C14,D6:D12,C15)**

Here, range **C6:C12 **contains the sold product name, **C14 **is the first criterion, **D6:D12** contains the selling date, and **C15** is the second criterion to count.

**📕 Read More: How to Find Column Index Number in Excel**

**Approach 2**

**2. Combining SUMPRODUCT and COUNTIFS Functions**

In this method, we will combine **SUMPRODUCT** and **COUNTIFS** functions to count one particular column if another column meets the particular criteria in Excel. The **SUMPRODUCT** function sums the products of related ranges or arrays. In addition, **COUNTIFS** counts the number of cells that meets multiple criteria. Thus, we will combine these two functions to count the total number of salespersons selling laptops. Let us see the steps.

**⬇️⬇️ STEPS ⬇️⬇️**

- First, in cell
**C15**insert the formula to have the number of salespersons who sold laptops.

**=SUMPRODUCT((C6:C12=C14)/COUNTIFS(B6:B12,B6:B12))**

**🔨 Formula Breakdown**

Here, the formula we inserted in cell **C15** is:

**SUMPRODUCT((C6:C12=C14)/COUNTIFS(B6:B12,B6:B12))**

`👉`

In this function, **C6:C12=C14 **looks into range **C6:C12 **and matches them with cell **C14**.

`👉`

**COUNTIFS(B6:B12,B6:B12)**, here **COUNTIFS** function counts in the range **B6:B12 **for the criteria **B6:B12**.

`👉`

Lastly, the **SUMPRODUCT** function sums the products of the value returned.

**📕 Read More: Count One Column If Another Column Meets Criteria in Excel**

**Approach 3**

**3. Utilizing Pivot Table**

Now, we will use a feature called **Pivot Table **to count one separate column if another one meets the specific criteria. For instance, we will count the total number of each product and the total number of sold products.

Let us see the steps now.

**⬇️⬇️ STEPS ⬇️⬇️**

- First, select the cells you want to create in the
**Pivot Table**. We selected cell**B5:D12**.

- Afterward, go to the Insert tab, click on Pivot Table from the Tables group of commands, and select
**From Table/Range**.

- Then, choose the Existing
**Worksheet**to place the table in the current worksheet and enter the cell reference where you want your pivot table. We typed**B14**in the**Location**and clicked on**OK**.

- Next, on the far right side of your sheet, you will see
**PivotTable Fields**named task option. Drag the**Product**option to the**Rows**area and**Values**.

- Finally, we will have our outcome illustrated in a Pivot Table.

**📕 Read More: 2 Handy Methods to Count Columns Until a Value Is Reached in Excel**

**Approach 4**

**4. Embedding VBA**

This method will demonstrate how to count one selected column if another one meets the particular criteria in excel using **Microsoft Visual Basic Application (VBA)**.

Also, ensure you have the **Developer** tab in the ribbon before applying this code. If you do not have the **Developer** tab, click here to see how to **enable the Developer tab on your ribbon**.

Now, let us go through the steps.

**⬇️⬇️ STEPS ⬇️⬇️**

- At first, go to the
**Developer**tab and select**Visual Basic**from the code group.

- Then, Microsoft Visual Basic for Application named box will appear. Go to the
**Insert**tab and select the**Module**.

- Enter the code and press
**F5**.

```
Sub CountIfs_Columns_with_Criteria()
Dim outcome As Double
outcome = Application.WorksheetFunction.CountIfs(Range("C6:C12"), "Laptop", Range("D6:D12"), "12/1/2022")
Range("C16").Value = outcome
End Sub
```

- Now, to run the code, press Alt+F8, select
**CountIfs_Columns_with_Criteria,**and then**Run**.

- Lastly, we will have the output.

## 📄 Important Notes

`🖊️`

To apply **VBA **codes, ensure you have the Developer tab in the ribbon before applying.

`🖊️`

** SUMPRODUCT** returns the **#VALUE!** Error value if the dimensions of the array argument do not match.

## 📝 Takeaways from This Article

`📌`

This article demonstrated 5 functions for counting one column established on criteria met by another column in excel.

`📌`

In the first approach, we showed **COUNTIF **and** COUNTIFS **functions to count one separate column if another column meets the criteria.

`📌`

Then, we demonstrated a formula that applies **SUMPRODUCT **and** COUNTIFS **functions to count one single column if another column meets the particular criteria in excel.

`📌`

Next, we utilized **Pivot Table **to count one individual column if another column meets the criteria.

`📌`

Finally, we embedded **VBA **code to count one individual column if another column meets the particular criteria in excel.

## Conclusion

This article has demonstrated 5 ways to count one particular column if another column meets the specific criteria in excel. All these formulas are incredibly effective and simple to use. We hope this article helps you. Feel free to leave a remark if you have any queries. The author will do their best to find an appropriate answer.

For more guides like this, visit **Excelden.com**.