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.


  • Go to cell E14 and insert the following formula.
=COUNTIF(C5:C12, C14)
  • Hit Enter.

Using COUNTIF Function to count one column if another column meets criteria

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.


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

Applying COUNIFS Function to count one column if another column meets criteria

Method 3 – Using the SUMPRODUCT Function


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

Using SUMPRODUCT Function to count one column if another column meets criteria

Method 4 – Utilizing a Pivot Table


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

Inserting Pivot Table

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

PivotTable from table or range window

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

Pivot Table fields

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

Utilizing PivotTable to count one column if another column meets criteria

Method 5 – Incorporating the CountIf Function in VBA


  • Go to the Developer tab and choose Visual Basic.

Developer tab

  • Go to the Insert tab and select Module.

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

VBA code for CountIf function

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

Incorporating CountIf Function in VBA

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

VBA code to use CountIfs function

  • Run the code with the F5 key.

Engaging CountIfs Function in VBA

Practice Section

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

Practice Section


Download the Practice Workbook

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

Get FREE Advanced Excel Exercises with Solutions!
Prantick Bala
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.


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

      Dear ABDUL KADAR

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


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

      Lutfor Rahman Shimanto

  2. Dear Mr. Shimanto,

    Thank you for your insightive tutorial.

    I would like to ask a follow up question based on your spreadsheet example.

    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,

    • Reply Lutfor Rahman Shimanto
      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.

      Lutfor Rahman Shimanto

Leave a reply

Advanced Excel Exercises with Solutions PDF