# 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.
`=COUNTIF(C5:C12, C14)`
• 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.
`=COUNTIFS(C5:C12,B15,D5:D12,C15)`

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.
`=SUMPRODUCT((D5:D12=C14)/COUNTIFS(B5:B12,B5:B12))`

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

• Hit Enter.

### Method 4 – Utilizing a Pivot Table

Steps:

• 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Â ``````

Code Breakdown:

Here, GR is the variable to show the output. We use the CountIf function in the active sheet. For the criteria Car, it will show criteria_range in the D5:D12 range. The MsgBox command shows the Total Number of Cars and the required output.

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

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

Get FREE Advanced Excel Exercises with Solutions!
Prantick Bala

PRANTICK BALA is a marine engineer who loves exploring Excel and VBA programming. Programming, to him, is a time-saving tool for efficiently managing data, files, and internet tasks. He is proficient in Rhino3D, Maxsurf, C++, MS Office, AutoCAD, Excel, and VBA and goes beyond the basics. Armed with a B.Sc in Naval Architecture & Marine Engineering from BUET, he has transitioned into the role of a content developer. Beyond his work, find him immersed in books, exploring new... Read Full Bio

1. Dear Sir,

In reference to your 2nd example “Applying COUNTIFS Function, please provide formula

“Jacksonville” howmany Product he made regardless product name, sometime product column is blank against jacksonville.

regards,

Lutfor Rahman Shimanto Apr 13, 2023 at 12:12 PM

Greetings from our website! Thank you for posting your question on the platform. As requested, I can assist you with an Excel formula using the IF and COUNTIFS functions that determine how many products a Region makes even if regardless of the product name (sometimes the product column is blank against a Region).

FORMULA:

=COUNTIFS(C5:C12, B15, D5:D12, IF(C15<>“”, C15, “”))

Regards
Lutfor Rahman Shimanto

2. Dear Mr. Shimanto,

Thank you for your insightive tutorial.

What would the formula be if I wanted to see what the total sales of each individual person be? In this example each “sales person” would have multiple entries.

Kind Regards,
Hendrik

Lutfor Rahman Shimanto Mar 27, 2024 at 5:38 PM

Hello Hendrik

Thanks for visiting our blog and posting your question. You must use the SUMIF or SUMIFS function to calculate the total sales.

1. Using SUMIF: `=SUMIF(\$B\$5:\$B\$12, \$H\$4, \$E\$5:\$E\$12)`
2. Using SUMIFS: `=SUMIFS(\$E\$5:\$E\$12,\$B\$5:\$B\$12,\$H\$4)`

I hope these formulas will help; good luck.

Regards
Lutfor Rahman Shimanto
ExcelDemy

Advanced Excel Exercises with Solutions PDF