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

Formula Syntax:

=COUNTIF(range, criteria)

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

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.

📌 Steps:

➤ In Cell F12, type-

=COUNTIF(B5:B19,"*")+COUNTIF(C5:C19,">0")+ COUNTIF(D5:D19,"<>"&"")

➤ Press Enter & you’ll notice that total 27 counts have been found from 3 columns under different criteria.

Countif-multiple-or-criteria-different-columns

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.

📌 Steps:

➤ Select Cell G15 & type-

=COUNTIFS($C$5:$C$19,"Notebook",$D$5:$D$19,"Lenovo",$E$5:$E$19,">40")

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

COUNTIF multiple criteria different columns


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.

📌 Steps:

➤ Go to Cell G15 & type-

=SUM(COUNTIFS($C$5:$C$19,{"Notebook","Desktop"},$D$5:$D$19, "Lenovo",$E$5:$E$19,">40"))

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

COUNTIF multiple criteria different columns with SUM function


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.

📌 Steps:

➤ Select Cell G15 & type-

=SUMPRODUCT((C5:C19=C5)*(D5:D19=D5)*(E5:E19>40))

➤ Press Enter & you’ll find the similar number of counts that we’ve got previously by using COUNTIFS function.

COUNTIF multiple criteria different columns alternative method SUMPRODUCT

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.


Concluding Words

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.


Related Articles You May Find Interesting

Multiple Criteria in Excel Using INDEX, MATCH, and COUNTIF Function

Finding out the number of duplicate rows using COUNTIF formula

How to Extract Unique Values Based on Criteria in Excel

Hello, Welcome to my profile. I'm a Technical Content Creator as well as a Naval Architect & Marine Engineer. I have preferences to do analytical reasoning & writing articles on various statistical data. Here, you'll find my published articles on Microsoft Excel & other topics related to my interests!

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo