Count One Column If Another Column Meets Criteria in Excel (4 Suitable Ways)

Suppose, you want to count one column based on criteria from another column in Excel. You can perform the task in different ways. In this article, I will introduce you to 4 suitable ways by which you will be able to count one column in Excel if another column meets criteria.

Consider the following dataset, Here sales information of an automobile selling company is given. Now we will Count No of salesmen (Count in Column A)  who are selling in a particular region (Criteria in Column B) or a particular product (Criteria in Column C).

Count column dataset

Download Practice Workbook

4 Ways to Count One Column in Excel If Another Column Meets Criteria

1. Using COUNTIF Function

You can count one column based on criteria in another column by using the COUNTIF function. Suppose we want to count the number of salesmen who sell in Jacksonville. To find out the number, type the formula in an empty cell,

=COUNTIF(B6:B13,F6)

Here, B6:B13 = Range of the dataset where the count takes place

          F6 = Criteria for counting, Jacksonville for our dataset

count column using counif

After pressing ENTER you will get the total number of salesmen who sell in Jacksonville in your selected cell.

count column using counif

Read More: How to Count Columns until Value Reached in Excel

2. Using COUNTIFS Function

The COUNIFS 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. Suppose we want to count the number of salesmen who sell in Jacksonville and who sell cars. To find out the number, type the formula in an empty cell,

=COUNTIFS(B6:B13,F6,C6:C13,F8)

Here,  B6:B13 = Range of the dataset for the first dataset

           F6 = First criteria for counting, Jacksonville for our dataset

           C6:C13 = Range of the dataset for the second dataset

           F8 = Second criteria for counting, Car for our dataset

count column based on another column

 After pressing ENTER you will get the total number of salesmen who sell in Jacksonville and who sell cars.

count column if another column meet criteria

Read More: How to Count Columns for VLOOKUP in Excel (2 Methods)

3. Using SUMPRODUCT Function

Using the SUMPRODUCT function is another way to count one column if another column meets the criteria. To find out the number of salesmen who sell cars, type the following formula in an empty cell,

=SUMPRODUCT((C6:C13=F6)/COUNTIFS(A6:A13,A6:A13))

Here, C6:C13 = Range of the dataset for criteria

         F6 = Criteria for counting, Car for our example

         A6:A13 = Range of cells where the count takes place

count column if another column meets criteria

After pressing ENTER you will get the total number of salesmen who sell cars.

count column based on another column

Read More: How to Convert Column Number to Letter in Excel (3 Ways)

4.   Using Pivot Table

If you have a very large dataset, using the Pivot Table can be a convenient way to find out the count of one column if another column or columns meet the criteria. For that, you have to create a Pivot table. First, select your data. Then go to Insert> PivotTable> From Table/Range.

creating pivot table

PivotTable from table or range box will appear. Select the box Existing worksheet and select an empty cell after clicking on the Location box. Then press OK.

creating pivot table

 

Now PivotTable Fields will appear on the right of your Excel. Drag the Salesman box in the ∑Values box. To find the number of salesmen in different regions drag the Region box in the Rows box.

count column using pivot table

To find the number of salesmen for different products, uncheck the Region box and check the Product box.

count one column based on the criteria in another column using pivot table

Read More: Excel VBA: Count Columns with Data (2 Examples)

Conclusion

You can use any of the described methods to count one column based on the criteria in another column. If you have any confusion please leave a comment. If you know any additional methods please let us know.


Further Readings

Prantick

Prantick

Hi, I'm Prantick Bala, currently working and doing research on Microsoft Excel in Softeko.com. I have completed BSc in Naval Architecture and Marine Engineering From Bangladesh University of Engineering and Technology (BUET). Currently I am in the last semester of my MBA at Institute of Business Administration (IBA), Dhaka University (DU). I'm always eager to learn through experience and have a deep interest in solving problem to get optimum solution.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo