How to Use COUNTIFS Function with 3 Criteria in Excel

Get FREE Advanced Excel Exercises with Solutions!

To count cells with multiple conditions, we use the COUNTIFS function. It can work with 127 pairs of ranges and criteria. So we can apply it to a wide range of different criteria. But in this article, we’ll show how to use the COUNTIFS function with 3 criteria in Excel. We’ll cover three useful examples with sharp steps and illustrations.


How to Use COUNTIFS Function with 3 Criteria in Excel: 3 Suitable Examples

Here’s the sample dataset that we’ll use to demonstrate the examples. It represents some salespersons’ sales and dates in different regions. Now we’ll show 3 examples to count the cells which met the three criteria- Region: East, Date: 4/1/2021, and Sales: >=20000.

How to Use COUNTIFS Function with 3 Criteria in Excel


Example 1: Insert 3 Criteria Directly in COUNTIFS Function

In our very first method, we’ll count the cells by inserting the three criteria directly by typing manually in the COUNTIFS function. That means we won’t use any cell references. But the disadvantage is, the formula gets longer for typing every condition.

Steps:

  • Activate Cell E18.
  • Then type the following formula in it-
=COUNTIFS(C5:C12,"East",D5:D12,"<=4/1/2021",E5:E12,">=20000")
  • Finally, just hit the Enter button to get the number of times that met all criteria. For our dataset, it’s returning the result- 2.

Insert 3 Criteria Directly in the COUNTIFS Function

Read More: Advanced Use of COUNTIFS Function in Excel


Example 2: Apply Criteria Using Ampersand and Cell Reference in COUNTIFS Function

Now in this example, we’ll insert the criteria by using the ampersand (&) and cell reference. Like, for “<=4/1/2021” we’ll type the less than or equal sign, then will use the cell reference of the date from the dataset. And then well insert ampersand ‘between them to join them. So finally, it will be “<=”&E15.

Steps:

  • In Cell E18, insert the following formula-
=COUNTIFS(C5:C12,E14,D5:D12,"<="&E15,E5:E12,">="&E16)
  • After that, just press the Enter button to get the counted cells that met the conditions.

Apply Criteria in the COUNTIFS Function Using Ampersand and Cell Reference


Example 3: Perform Criteria Using Cell Reference Only

In our last example, we’ll show how to use the COUNTIFS function with 3 criteria and this time we’ll insert the three criteria by using the cell references only. That means, no need to type anything manually in the formula.

For that, we modified the criteria list in the dataset. We inserted the ‘less than or equal to’ sign and the ‘greater than or equal to’ sign in the dataset. So there is no need to apply those signs in the formula rather we’ll just insert the cell references. The advantage is, the formula will be shorter in length compared to the first and second examples.

Steps:

  • Write the following formula in Cell E18
=COUNTIFS(C5:C12,E14,D5:D12,E15,E5:E12,E16)
  • Then to get the output, just hit the Enter button and the formula will return the same output as the previous examples.

Read More: How to Use COUNTIFS with Date Range in Excel


Remarks

  • The highest 127 pairs of criteria range and criteria can be inserted.
  • If any cell reference for the criteria remains empty then the COUNTIFS function will consider it zero.

Download Practice Workbook

You can download the free Excel workbook from here and practice independently.


Conclusion

That’s all for the article. I hope the above procedures will be good enough to use the COUNTIFS function with 3 criteria in Excel. Feel free to ask any questions in the comment section and give me feedback.


Related Article


<< Go Back to Excel COUNTIFS Function | Excel Functions | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Sourov Hossain Mithun
Md. Sourov Hossain Mithun

Md. Sourov Hossain Mithun, an Excel and VBA Content Developer at Softeko's ExcelDemy project, joined in October 2021. Holding a Naval Architecture & Marine Engineering degree from BUET, Mithun showcases expertise during his two-year tenure. With over 200 published articles on Excel topics, he earned a promotion to Team Leader, excelling in leading diverse teams. Mithun's passion extends to Advanced Excel, Excel VBA, Data Analysis, and Python programming, contributing significantly to the innovative and dynamic environment of ExcelDemy... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo