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.
Arguments of the Formula:
Counts the number of cells within a range that meet the given conditions.
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.
- 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.
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.
- 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.
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.
- 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.
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.
- 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.
=AND(C5= "Notebook",D5= "Lenovo",E5>40)
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.
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.
- 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.
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.
- 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.
Now, you can practice the explained method by yourself.
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.
- 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