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.

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

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

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.

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

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.

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

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.

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

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

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.

Related Articles

• 2 Ways to Count Columns for VLOOKUP in Excel