Count One Column If Another Column Meets Criteria in Excel

While using Excel, you may need to count for different values based on some specific criteria. Excel shines with enormous features to count different columns. Moreover, sometimes in your dataset, you may count one column which meets the criteria with another column. We used different functions and VBA codes to do that. Go through this article, and you will get a clear concept to count one column if another column meets the criteria. So, let’s get started.

Count One Column If Another Column Meets Criteria in Excel: 6 Methods

For counting the numbers of different criteria, you need to insert a dataset of multiple columns. Here, we have taken a dataset of the Sales Report of XYZ Company with different information in multiple columns. Now, we will count one column if another column meets the Excel criteria.

Not to mention, we have used the Microsoft 365 version. You may use any other version at your convenience.

1. Using COUNTIF Function

You can count one column based on criteria in another column by using the COUNTIF function. This function shows the count for the criteria you entered as an argument. Suppose we want to count the number of salesmen who sell in Jacksonville from the above dataset. To find it follow the below procedure.

📌 Steps:

• Firstly, move to cell E14, and input the following formula.

Here, C5:C12 refers to the range of the dataset where the count takes place. C14 represents the criteria for counting, Jacksonville for our dataset. In this formula, the COUNTIF(C5:C12, C14) syntax counts for the insert criteria for the C14 cell in the criteria_range of C5:C12.

• Then, press ENTER,

Eventually, you will get the total number of salesmen who sell in Jacksonville in your selected cell.

2. Applying COUNTIFS Function

The COUNTIFS function is used when the count is made based on multiple criteria. You can count one column based on criteria in multiple columns using the COUNTIFS function. We want to count the number of salesmen selling “Car” in Jacksonville using our dataset. Follow the below steps to do that.

📌 Steps:

• Initially, move to cell D15 and insert the formula.

Here, C5:C12 is the cell range of the dataset for the first dataset, B15 refers to the first criteria for counting, Jacksonville for our dataset, D5:D12 is the range of the dataset for the second dataset, C15 depicts “Car”, the second criteria for counting. The COUNTIFS function counts for the multiple criteria of B15 and C15 cells in the criteria_range of C5:C12 and D5:D12.

Consequently, press ENTER, and you will get the following result, like the image below.

3. Employing SUMPRODUCT Function

The SUMPRODUCT function is another way to count one column if another column meets the criteria. This function counts the total value for special criteria. Follow the steps to use the function.

📌 Steps:

• Firstly, go to cell E14 and input the below formula.

The SUMPRODUCT function counts the total number of cars in the D5:D12 range.

Subsequently, press ENTER, and you will get the below output.

4. Utilizing Pivot Table

If you have a vast dataset, using the PivotTable can be a convenient way to find out the count of one column if another column or columns meet the criteria. Follow the steps to do that.

📌 Steps:

• Primarily, you have to create a PivotTable. For that, select your entire dataset >> navigate go to Insert > select PivotTable > pick From Table/Range.

• Sequentially, the PivotTable from table or range box will appear. Then check the Existing Worksheet box and select an empty cell after clicking on the Location box. Lastly, press OK.

• Moreover, the PivotTable Fields appear on the right of your Excel. Drag the Salesman box into the ∑Values box. To find the number of salesmen for different products, drag the Product box into the Rows field.

Finally, you get the table where the values existed, and you get the total numbers of the Salesman.

5. Incorporating CountIf Function in VBA

However, you can use VBA macros to insert the VBA CountIf function. We insert the function commands to get the related result. Follow the steps to insert the VBA code.

📌 Steps:

• Firstly, hover over the Home tab >> choose Visual Basic.

• Additionally, move to the Insert tab >> Module >> Module1.

• Write the following code in the Module 1 box.

Sub CountIf_with_VBA()
Dim GR As Double
GR =Application.WorksheetFunction.CountIf(Range("D5:D12"), "Car")
MsgBox "Total Numbers of Car: " & GR
End Sub 

• Eventually, run the code with the F5 key and get the following output.

6. Engaging CountIfs Function in VBA

Similarly, you can use the VBA CountIfs function as well. The procedure for opening Visual Basic Module 1 is the same as Method 5. The above function works for multiple criteria. You can enter the following code, as stated below, to use the function. The criteria_range here is C5:C12 and D5:D12. The MsgBox command shows the output as a message.

Sub CountIfs_with_VBA()
Dim GR As Double
GR = Application.WorksheetFunction.CountIfs(Range("C5:C12"), "Jacksonville", Range("D5:D12"), "Car")
MsgBox "Total Numbers of Car of Jacksonville: " & GR
End Sub

• Lastly, run the code with the F5 key, and get the below output.

Practice Section

We have provided a practice section on each sheet on the right side for your practice. Please do it by yourself.

Download Practice Workbook

Conclusion

That’s all about today’s session. These are some easy methods to count one column if another column meets the criteria in Excel. Please let us know in the comments section if you have any questions or suggestions. For a better understanding, please download the practice sheet. Visit our website ExcelDemy, a one-stop Excel solution provider, to find out about diverse kinds of Excel methods. Thanks for your patience in reading this article.

<< Go Back to Count Columns | Formula List | Learn Excel