# Count One Column If Another Column Meets Criteria in Excel

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.

## 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.

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. 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.

`📌` 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.

## Related Articles

(Visited 73 times, 1 visits today) #### Priti

Hello! Welcome to my Profile. I am Priti. I'm now working and researching Microsoft Excel, and I'll share articles on it here. I am passionate about problem-solving, and I hope these articles will help you. Please leave a remark if you have any queries, and I will do my best to find an appropriate answer.

We will be happy to hear your thoughts 