How to Use the COUNTIFS Function in Microsoft Excel

Do you have a scenario where you want to count the number of cells that match specific conditions? I’m sure you do!  There is a simple way to count this with Excel’s COUNTIFS formula. This is very similar to the CountIf Formula. The only difference is it allows you to add even more conditions as needed. That’s POWEFUL! In this article, we will be looking at how to use the COUNTIFS function in Excel. The COUNTIFS function in Excel is an extremely useful tool when you want to count things based on multiple conditions or criteria.

Key Takeaways:

• COUNTIFS counts with multiple criteria – It allows counting cells that meet two or more conditions across different ranges.
• Supports both numbers and text – Works seamlessly with numeric, text, and logical conditions like >, <, or =.
• Flexible with wildcards – Use *, ?, or ~ to match partial text or literal wildcard characters.
• Cell references improve flexibility – Using references instead of hardcoded values lets you easily update criteria.
• Handles advanced logical conditions – You can combine multiple conditions, including ranges and comparisons, for precise counting.

Download the Excel Workbook below to follow along and understand How to Use the COUNTIFS Function in Microsoft Excel –

download excel workbookCOUNTIFS.xlsx

 

Understanding Countifs Function

What is the COUNTIFS Function?

The COUNTIFS function in Excel is used to count the number of cells that meet multiple criteria across one or more ranges. While the COUNTIF function allows you to count cells based on a single condition, COUNTIFS extends this functionality by letting you apply two or more conditions at the same time. It works with numbers, text, and logical operators like >, <, and =.

You can even use wildcards such as * and ? to match partial text, making it highly flexible for real-world data.

Syntax of COUNTIFS

=COUNTIFS(range1, criteria1,[range2], [criteria2],…)

• COUNTIFS: The name of the function
• range1, range2:  The ranges of cells where Excel will look for the data. Multiple ranges can be separated by commas. Each range represents a column or a group of cells in your table.
• criteria1, criteria2:  The conditions or criteria that Excel will use to determine which items to count. You provide one criteria for each range. Each criterion can be a value, a cell reference, a text string, or a logical expression.

(Remember that you can use up to 127 range and criteria pairs in COUNTIFS. Now, that’s powerful!)

 

Basic Application

Example: Counting Race Participants

Let’s say you have a table containing the results of a marathon race. Column A contains the time the competitors finished, column B contains the names of the competitors, column C is their respective districts, and column D is whether they completed the race or did not complete the race. We will use 2 range and criteria for this example.

We want to find out how many competitors from the Wellington district finished the race.

The formula will be:

=COUNTIFS(C2:C25,”wellington”,D2:D25,”finished”)

• First criteria: C2:C25 range. The criteria we want them to meet are that they should be from the Wellington region. Since it is a text value, we enclose that within double quotes.
• Second criteria: D2:D25 range. We want to know how many of them finished the race.

As you can see, the results return as 3. Meaning 3 racers from the Wellington region finished the race.

Cell References

In that example, we used the hardcoded values as part of the formula. But, it is usually better to use cell references for flexibility reasons. If we use cell references, we do not have to change the formula whenever we change the values entered in the input cell.

Here’s how it’s done:

District: Wellington

Status: Finished

Count: =COUNTIFS(C2:C25,F2,D2:D25,F3)

As you can see, we get the same results as the hardcoded values. But, the big advantage is that we can change the criteria easily without having to change the formula.

 

Advanced Techniques

Using Logical Operators

The COUNTIFS function allows us to count not just exact matches but also include cells that are greater than, less than, or not equal to certain values. By using logical operators like > (greater than), < (less than), and = (equal to), we can specify criteria to identify cells with values within a specific range.

For example, the formula below can be used to count the number of racers from the Dundas district who completed the race under 3.5 hours.

=COUNTIFS(C2:C25,F2,A2:A25,”<“&F3)

As you can see, the results returned only one person (Leticia Korver) from the Dundas district who ran the race under 3.5 hours.

If we did not want to use cell references and wanted to use hardcoded values instead, we could use this formula:

=COUNTIFS(C2:C25,”Dundas”,A2:A25,”<210”)

Wildcards

When it comes to summarizing data, we often encounter a challenge where the data we want to count is similar but not exactly the same. However, a great alternative to overcome this issue is to use WILDCARDS. Thankfully, the COUNTIFS function in Excel supports the use of wildcards, allowing us to perform counts with partial matches.

For example, we want to count the number of jackets in the women’s department. The problem is, there are several types of jackets like denim, fleece, and leather listed in the inventory. We can use the asterisk sign (*) before the word “jacket”

Let’s use this formula:

=COUNTIFS(A2:A15,”*jacket”,C2:C15,”women’s”)

You can include wildcard characters such as the question mark (?), asterisk (*), or tilde (~) in your criteria when using the COUNTIFS function. The question mark (?) can be used to represent any single character, while the asterisk (*) can represent zero or more characters of any kind.

The tilde (~) serves as an escape character, enabling you to search for wildcard characters as literal characters within the COUNTIFS function.

 

Additional Real-World Example

The COUNTIFS formula is very flexible indeed, so let us try to count the following from our Excel worksheet:

• Number of times John got more than 10,000 sales
• Number of times Kim got more than 18,000 sales

STEP 1: Let us target the first question: How many times John got more than 10,000 sales?

We need to enter the COUNTIFS function in a blank cell:

=COUNTIFS(

STEP 2: The COUNTIFS arguments:

range1, criteria1

What is our first condition?

We want to find the names that match “John”

=COUNTIFS(A9:A13, “John”,

range2, criteria2

What is our second condition?

We want to find sales that are more than 10,000

=COUNTIFS(A9:A13, “John”, C9:C13, “>10000”)

You now have your count of 2!

STEP 3: Now let us try doing the same for Kim!

range1, criteria1

What is our first condition?

We want to find the names that match “Kim”

=COUNTIFS(A9:A13, “Kim”,

range2, criteria2

What is our second condition?

We want to find the sales that are more than 18,000

=COUNTIFS(A9:A13, “Kim”, C9:C13, “>18000”)

You now have your count of 1!

You can have more than 2 conditions in the COUNTIFS formula, so go crazy with the COUNTIFS!

 

FAQs

1. How can I count multiple conditions in Excel?

You can use the COUNTIFS function to count cells that meet multiple conditions. Specify one range and criteria pair for each condition. For example, =COUNTIFS(A2:A10, “>5”, B2:B10, “Yes”) counts cells where A>5 and B=”Yes”. COUNTIFS supports up to 127 range/criteria pairs. It’s ideal for complex data analysis.

2. Can COUNTIFS handle text and numbers together?

Yes, COUNTIFS works with both text and numbers simultaneously. You can count cells based on text strings, numeric ranges, or logical operators. For example, =COUNTIFS(A2:A10, “John”, B2:B10, “>100”) works perfectly. Text criteria are not case-sensitive. Logical operators like >, <, and = are fully supported.

3. How do I use wildcards in COUNTIFS?

Wildcards make your criteria more flexible. Use * for multiple characters, ? for a single character, and ~ to treat wildcards literally. For example, =COUNTIFS(A2:A10, “*jacket”) counts any cell ending with “jacket”. This is useful for partial matches in large datasets.

4. Can I apply OR logic in COUNTIFS?

COUNTIFS only supports AND logic directly. To achieve OR logic, combine multiple COUNTIFS formulas with the SUM function. For example, =SUM(COUNTIFS(A2:A10,”red”), COUNTIFS(A2:A10,”blue”)) counts cells that are either red or blue. Each COUNTIFS handles one condition, then SUM totals the results.

5. What are COUNTIFS limitations?

COUNTIFS cannot handle case sensitivity or arrays directly, and very large numbers may be inaccurate. All conditions are combined using AND logic, so OR needs a workaround. It also requires actual ranges, not calculated arrays. For advanced scenarios, consider SUMPRODUCT or Excel’s BYROW/BYCOL functions.