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, I’ll try to illustrate how we can use these two fruitful functions to count cells with different criteria from distinct 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.
Uses of COUNTIF & COUNTIFS with Multiple Criteria in Different Columns
Before getting down to the uses of COUNTIF function, let’s have a look at the introductions of this function.
Function 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.
COUNTIFS is a sub-category of COUNTIF function & it’s capable of incorporating more than one criteria.
Well, now we can start on the uses of COUNTIF as well as COUNTIFS functions with relatable datasets.
1. Incorporating More Than One 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.
There are 3 slots in our dataset. All of them have lists of distinct names & ID’s. We have to count only names from Slot A, only ID’s from Slot B & all-blank cells from Slot C. Then we’ll make a sum of all counts under these categories.
➤ In Cell F12, type-
➤ Press Enter & you’ll notice that total 27 counts have been found from 3 columns under different criteria.
COUNTIF functions here have been used for all 3 slots separately. Then we’ve simply added all these counts by inserting Plus(‘+’) in the function bar.
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. 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 COUNTIFS by combining multiple criteria.
➤ Select Cell G15 & type-
➤ Press Enter.
You’ll see only 2 instances for more than 40 sales of Lenovo Notebook. 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 COUNTIF & SUM Functions with Multiple Criteria in Separate Columns
Now we want to know the number of sales more than 40 for both device categories of Lenovo brand.
➤ Go to Cell G15 & type-
➤ 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.
An Alternative to COUNTIF Functions to Count Cells With Multiple Criteria in Different Columns
Let’s go back to our 1st dataset where we had to find the total counts for more than 40 sales of Lenovo Notebooks over 3 months. We can get the similar result by applying SUMPRODUCT function too.
➤ Select Cell G15 & type-
➤ Press Enter & you’ll find the similar number of counts that we’ve got previously by using 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 COUNTIFS function but in SUMPRODUCT function you have to use Asterisks(*) to assign the similar role.
I hope all these simple methods under different criteria I’ve described will help you to understand when & how to use COUNTIF along with COUNTIFS functions properly in your own datasets. If you think I’ve missed a point or a method I should’ve put in then please let me know in the comment section. I’ll update the article soon following your valuable recommendations.