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:

• The COUNTIFS function in Microsoft Excel allows for counting the number of cells that meet multiple criteria across different ranges.
• The COUNTIFS function can utilize wildcard characters to create flexible criteria for counting cells.
• The COUNTIFS function not only performs multi-condition checks but also functions effectively with both textual and numerical criteria, offering a comprehensive toolkit for data analysis in diverse scenarios.

Syntax:

=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!)

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

download excel workbookCOUNTIFS.xlsx

Basic Application

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

COUNTIFS with logical criteria

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 can 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 About Using COUNTIFS in Excel

How Can I Use COUNTIFS to Count Non-Numeric Data?

COUNTIFS is versatile and can indeed count non-numeric data. You simply specify the criteria for the text values you’re interested in. For example, to count occurrences of the word “apple”, your formula would be =COUNTIFS(range, "apple"). It’s straightforward but remember that COUNTIFS isn’t case sensitive.

What Are the Limitations of COUNTIFS and How Do I Work Around Them?

COUNTIFS indeed has its set of constraints. All the conditions specified in the function are combined with an AND logic, meaning every single criterion must be true for a cell to be counted. You’ll find that COUNTIFS requires actual ranges rather than arrays, avoiding any pre-condition manipulation of values. For those lengthy numbers exceeding 15 digits, COUNTIFS won’t count them accurately. Also, case sensitivity isn’t in its nature either.

Overcoming these limitations might require switching to SUMPRODUCT when case sensitivity is crucial or handling OR logic. Excel’s newer BYROW and BYCOL can also offer more flexible alternatives for tackling complex counting scenarios.

Can I Combine OR Logic within a Single COUNTIFS Formula?

Directly, COUNTIFS doesn’t support OR logic since it’s designed to work with AND conditions. However, you can combine OR logic by using multiple COUNTIFS in conjunction with the SUM function. For example, to count cells that contain either “red” or “blue,” use =SUM(COUNTIFS(range, "red"), COUNTIFS(range, "blue")). Each COUNTIFS handles one condition, and SUM totals them up, effectively working as an OR.

There you have it! The COUNTIFS function gets rid of the necessity to create complex formulas or pivot tables for answering simple questions. Its syntax is straightforward to understand, and its ability to work with wildcards adds an extra advantage.