COUNTIF with Multiple Criteria in Different Columns in Excel

Microsoft Excel has provided very useful & common functions named COUNTIF & COUNTIFS to count cells under multiple criteria in different columns or rows. In this article, we’ll try to illustrate how we can use these two fruitful functions to count cells with multiple criteria from different columns.

Download Practice Workbook

You can download our Excel Workbook that we’ve used to prepare this article. You can input or change data to find different results under embedded formulas in the resultant cells.

5 Uses of COUNTIF Function with Multiple Criteria in Different Columns in Excel

Before getting down to the uses of the COUNTIF function, let’s have a look at the introductions of this function.

Formula Syntax:

Arguments of the Formula:

Counts the number of cells within a range that meet the given conditions.

Arguments:

range– A range of cells along columns or rows or both that needs to be counted.

criteria– Given conditions under which the function will work to count cells.

Furthermore, COUNTIFS is a sub-category of the COUNTIF function & it’s capable of incorporating more than one criterion.

Well, now we can start on the uses of COUNTIF as well as COUNTIFS functions with relatable datasets.

1. Incorporating Multiple COUNTIF Functions with Multiple OR Criteria in Distinct Columns

Here’s a dataset where we’ll apply multiple COUNTIF functions to count data under different criteria in different columns.

Basically, there are 3 slots in our dataset. All of them have lists of distinct names & IDs. We have to count only names from Slot A, only IDs from Slot B & all-blank cells from Slot C. Then we’ll make a sum of all counts under these categories.

📌 Steps:

• Firstly, in Cell F12, type-

Here, the COUNTIF functions here have been used for all 3 slots separately. Then we simply added all these counts by inserting Plus (‘+’) in the function bar.

• Subsequently, press ENTER.

As a result, you’ll notice that total of 27 counts have been found from 3 columns under different criteria.

Read More: How to Apply SUM and COUNTIF for Multiple Criteria in Excel

2. Use of COUNTIFS Function with Multiple Criteria in Different Columns

We have now another dataset where sales units of different brand devices in 3 months are stored. Now, we want to find a specific count under multiple criteria. If we want to count the number of Lenovo notebooks with more than 40 sales in those 3 months, then we can use the COUNTIFS function by combining multiple criteria.

📌 Steps:

• Firstly, select Cell G13 & type-

• After that, press ENTER.

Lastly, you’ll see only 2 instances of more than 40 sales of the Lenovo Notebook. So, this formula is effective enough when you have to count specific data under multiple criteria from a large number of cells or a large table.

Read More: How to Use COUNTIFS to Count Across Multiple Columns in Excel

3. Combination of COUNTIFS & SUM Functions in Separate Columns

Here, we want to know the number of sales of more than 40 for both device categories of the Lenovo brand. In addition, we’re going to use the combination of COUNTIFS, and SUM functions.

📌 Steps:

• Firstly, go to Cell G13 & type-

• Subsequently, press ENTER & you’re done.

So, now you’re seeing the Total Counts- 4 as there are 4 instances of Lenovo devices that have been sold more than 40 units over 3 specific months in a year.

Read More: How to Use COUNTIF with Multiple Criteria in the Same Column in Excel

4. Use of AND and COUNTIF Functions for Applying Multiple Criteria

Here, we will use AND, and COUNTIF functions for multiple criteria in different columns in Excel. Suppose we want to find a specific count under multiple criteria. Additionally, let’s count the number of Lenovo notebooks with more than 40 sales in 3 months. The steps are given below.

📌 Steps:

• Firstly, you have to select a new cell F5 where you want to keep the Status.
• Secondly, you should use the formula given below in the F5 cell.

Here, in this formula, the AND function will return TRUE if the cell value of C5 is “Notebook”, the cell value of D5 is “Lenovo”, and the cell value of E5 is greater than 40.

• Thirdly, press ENTER to get the result.

• After that, you have to drag the Fill Handle icon to AutoFill the corresponding data in the rest of the cells F6:F19. Or you can double-click on the Fill Handle icon.

Finally, you will get the Status. This means you will come to know whose cells fulfill that logic.

• Then, write down the following formula in the H13 cell.

Here, in this formula the COUNTIF function will count those cells which contain TRUE as a value.

• After that, press ENTER.

Finally, you will get the 2 instances for more than 40 sales of Lenovo Notebook.

Read More: COUNTIF Between Two Values with Multiple Criteria in Excel

5. Applying COUNIF Function as Array

Here, we will use the COUNTIF function as an array for multiple criteria in different columns in Excel. Suppose, we want to count the total number of Lenovo notebooks with more than 40 sales in the 3 months. The steps are given below.

📌 Steps:

• Firstly, select a new cell H8 where you want to keep the result. Here, you should keep blank cells next to the H8 cell (vertically). Furthermore, the blank cells should be equal to the number of given criteria.
• Secondly, you should use the formula given below in the H8 cell.

Here, the COUNTIF function will count cells from the C5:E19 data range, which will fulfill the given condition. Additionally, G8:G10 is the criteria range.

• Subsequently, press ENTER to get the result.

Finally, you will get the total counts for individual criteria.

Read More: How to Apply COUNTIF Not Equal to Text or Blank in Excel

How to Use SUMPRODUCT Function to Count Cells with Multiple Criteria in Different Columns in Excel

Let’s go back to our 2nd dataset where we had to find the total counts for more than 40 sales of Lenovo Notebooks over 3 months. Here, we can get a similar result by applying the SUMPRODUCT function too.

Now, let’s see the steps.

📌 Steps:

• Firstly, select Cell G13 & type-

• After that, press ENTER & you’ll find the similar number of counts that we’ve got previously by using the COUNTIFS function.

While counting instances under multiple criteria in different columns, the difference between COUNTIFS & SUMPRODUCT functions is that you have to use Commas (,) to add multiple criteria in the COUNTIFS function but in the SUMPRODUCT function, you have to use Asterisks (*) to assign a similar role.

Read More: SUMPRODUCT and COUNTIF Functions with Multiple Criteria

Practice Section

Now, you can practice the explained method by yourself.

Concluding Words

We hope all these simple methods under multiple criteria in different columns, we’ve described, will help you to understand when & how to use the COUNTIF along with COUNTIFS functions properly in your own datasets. Now, if you think we’ve missed a point or a method we should’ve put in then please let us know in the comment section. Then, we’ll update the article soon following your valuable recommendations.

Related Articles

• Apply COUNTIF Function in Multiple Ranges for Same Criteria
• INDEX, MATCH, and COUNTIF Functions with Multiple Criteria
• How to Use COUNTIF Function Across Multiple Sheets in Excel
• Excel COUNTIF Function with Multiple Criteria & Date Range
• How to Use Excel COUNTIF That Does Not Contain Multiple Criteria
• How to Use COUNTIF Between Two Dates and Matching Criteria in Excel