How to Use COUNTIFS Function with 3 Criteria in Excel

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.

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.

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.

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.

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.

3 Criteria in COUNTIFS Function.xlsx

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.