Count One Column If Another Column Meets Criteria in Excel

We have a dataset of the Sales Report of a Company. We will count one column if the values in another column meet certain criteria.

Method 1 – Using COUNTIF Function

We want to count the number of salesmen who sell in Jacksonville.

Steps:

• Go to cell E14 and insert the following formula.

• Hit Enter.

Method 2 – Applying the COUNTIFS Function

We want to count the number of salesmen selling Car (in C15) in Jacksonville (in B15) using our dataset.

Steps:

• Move to cell D15 and insert the following formula.

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 contains Car, the second criteria for counting.

• Hit Enter.

Method 3 – Using the SUMPRODUCT Function

Steps:

• Go to cell E14 and input the following formula.

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

• Hit Enter.

Method 4 – Utilizing a Pivot Table

Steps:

• Select your entire dataset.
• Go to Insert, select PivotTable, and pick From Table/Range.

• The PivotTable from table or range box will appear. Check the Existing Worksheet box and select an empty cell after clicking on the Location box.
• Press OK.

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

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

Method 5 – Incorporating the CountIf Function in VBA

Steps:

• Go to the Developer tab and choose Visual Basic.

• Go to the Insert tab and select Module.

• Insert 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 

• Run the code with the F5 key to get the following output.

Method 6 – Using the CountIfs Function in VBA

• Use the following VBA code in a module.

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

• Run the code with the F5 key.

Practice Section

We have provided a practice section on each sheet on the right side so you can test these methods.

Download the Practice Workbook

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