COUNTIFS Function

The COUNTIFS function counts cells in a range when they meet one or more specific conditions. COUNTIFS is one of Excel's most widely used functions, and you find it in all kinds of spreadsheets that perform conditional counts based on dates, text, or numbers. Although common, COUNTIFS has a unique design that splits logical conditions into two parts. As a result, the syntax to define conditions in COUNTIFS can be a little tricky and takes some getting used to. Remember that COUNTIFS uses "AND logic" — to be included in the final count, all conditions must be TRUE.

Key features

• Counts values that meet one or more conditions
• Each new condition requires a new range and criteria
• Works with dates, text, and numbers
• Supports comparison operators (>, <, <>, =) and wildcards (*, ?)
• To be included in the final result, all conditions must be TRUE
• Will return a #VALUE! error if ranges are not the same size
• Available in all Excel versions since Excel 2007

Unlike most other Excel functions, COUNTIFS requires an actual range for criteria ranges. If you try to use an array, Excel will not let you enter the formula. For details, see the example below.

Table of Contents

• Syntax
• Basic Example
• Applying Criteria
• Criteria in another cell
• Not equal to
• Blank cells
• Dates
• Wildcards
• OR logic
• Summary Table
• Array Problem
• Limitations
• Notes

Syntax

The COUNTIFS function counts the number of cells in a range that meet one or more conditions. The syntax depends on the number of conditions being evaluated. Each condition is provided as a pair of range/criteria arguments:

=COUNTIFS(range,criteria) // 1 condition
=COUNTIFS(range,criteria,range,criteria) // 2 conditions

If there are two conditions, there will be two range/criteria pairs. If there are three conditions, there will be three range/criteria pairs, and so on. 

All ranges must be the same size. If you provide different sizes, COUNTIFS will return #VALUE!

Basic Example

The worksheet below simple contains order data, including Date, Color, State, Quantity, and Total. Using COUNTIFS, we can easily count orders in the data that meet the following conditions:

1. Count orders where the color is Red.
2. Count orders where the color is Red, and the state is TX.
3. Count orders where the color is Red, and the total is over 20.
4. Count orders where the color is Red, the total is over 20, and the state is TX.

The four formulas in I5:I8 look like this:

=COUNTIFS(C5:C16,"red")
=COUNTIFS(C5:C16,"red",D5:D16,"TX")
=COUNTIFS(C5:C16,"red",F5:F16,">20")
=COUNTIFS(C5:C16,"red",F5:F16,">20",D5:D16,"TX")

The meaning of the formula in cell I8 is: count rows where the color is "red" AND the quantity is >20 AND state is "TX". The result is 2 since there are two rows where the color is Red, the quantity is over 20, and the state is TX. Each condition requires a separate range/criteria pair, and there are three pairs in total. To summarize, here are a few things worth noting in the formulas above:

• Each condition requires a separate range/criteria pair.
• All range arguments must be the same size.
• The order in which conditions appear does not matter.
• Criteria can include logical operators (>,<,<>,<=,>=) as needed.
• An equal sign (=) is not needed for "is equal to" criteria (i.e. use "red" not "=red").
• COUNTIFS is not case-sensitive, so "red" and "Red" will return the same results.
• Numbers must appear in quotes ("") when used with operators (i.e. ">20").
• COUNTIFS uses "AND logic," and all conditions must be true.

The syntax for the criteria argument in COUNTIF is somewhat unusual in Excel. Instead of simply entering >20 as the criteria, you must enter ">20" in double quotes.  If you don't quote values as required, Excel will not let you enter the formula. See below for more examples of this syntax.

Applying Criteria

The COUNTIFS function supports logical operators (>,<,<>,=) and wildcards (*,?) for partial matching. Because COUNTIFS is in a group of eight functions that split logical criteria into two parts, the syntax is a bit tricky. Each condition requires a separate range and criteria, and operators need to be enclosed in double quotes (""). The table below shows some common examples:

Notice the last two examples use concatenation with the ampersand (&) character. When a criteria argument includes a value from another cell, or the result of a formula, logical operators like "<" must be joined with concatenation. This is because Excel needs to evaluate cell references and formulas first to get a value before that value can be joined to an operator.

Criteria in another cell

It is often convenient to put criteria in another cell and then refer to this cell inside your formula. This makes it easy to change criteria later without editing the original formula. For example, you can count cells in a range equal to the value in A1 like this:

=COUNTIFS(range,A1)

When a condition requires an operator, you must concatenate the cell reference to the operator. For example, to count cells in a range greater than A1, use a formula like this:

=COUNTIFS(range,">"&A1)

Note we are joining the ">" operator to cell A1 with an ampersand (&) character. In the worksheet below, COUNTIFS has been configured to return the count of all sales over the value in G4. Notice the greater than operator (>), which is text, must be enclosed in quotes. The formula in G5 is:

=COUNTIFS(D5:D9,">"&G4) // count if greater than G4

Don't enclose cell references in double quotes like "A1". Doing so will convert them to text.

Not equal to

To express "not equal to" criteria, use the "<>" operator surrounded by double quotes (""). For example, use "<>red" for "not red", and "<>blue" for "not blue", as seen in the worksheet below:

=COUNTIFS(B5:B9,"<>red") // not red
=COUNTIFS(B5:B9,"<>blue") // not blue
=COUNTIFS(B5:B9,"<>"&E7) // not E7

Note the following:

• In the last formula, we use E7 directly, so we need to concatenate like "<>"&E7.
• Do not use quotes around the cell reference.
• COUNTIFS is not case-sensitive, so "red", "RED", and "Red" will return the same result.

Blank cells

COUNTIFS can count cells that are blank (empty) or not blank (not empty). Use an empty string ("") to target blank cells and the "not equal to" operator ("<>") to target cells that are not blank. In the worksheet below, COUNTIFS is configured to count blank and not blank cells in column D:

=COUNTIFS(D5:D9,"") // blank
=COUNTIFS(D5:D9,"<>") // not blank

In the first format, we use an empty string ("") for the criteria, which will count empty cells. In the second formula, we use the "not equal to" operator ("<>"), which will count non-empty cells. Note that the second formula will count cells that contain any value. If you want to be more precise, you can adjust the criteria as follows:

=COUNTIFS(D5:D9,"x")

This formula will count only cells that contain an "x".

Double quotes ("") in criteria

In general, text values need to be enclosed in double quotes, and numbers do not. However, when a logical operator is included with a number, the number and operator must be enclosed in quotes as shown below:

=COUNTIFS(range,100) // count equal to 100
=COUNTIFS(range,">50") // count greater than 50
=COUNTIFS(range,"jim") // count equal to "jim"

Note: Additional conditions must follow the same rules.

Dates

In Excel, dates are serial numbers, so you can use operators like <,>, <=, >= with dates like any other number. The tricky part about using dates in COUNTIFS conditions is entering the dates in a way that Excel will understand. The easiest way to use COUNTIFS with dates is to refer to a valid date in another cell. For example, with a date in A1, you can count dates that are greater than A1 like this:

=COUNTIFS(range,">"&A1) // count dates greater than A1

If you want to hardcode a date into the COUNTIFS function, the best way is to use the DATE function. For example, to count dates greater than 1-Jan-2024, you can use COUNTIFS like this:

=COUNTIFS(range,">"&DATE(2024,1,1) // count dates greater than 1-Jan-2024

The worksheet below uses both methods:

The formulas in F5:F8 are as follows:

=COUNTIFS(B5:B12,"<"&DATE(2024,3,1))
=COUNTIFS(B5:B12,">="&DATE(2024,3,31))
=COUNTIFS(B5:B12,">"&E10)
=COUNTIFS(B5:B12,">="&DATE(2024,5,1),B5:B12,"<="&DATE(2024,5,31))

Note the following:

• When using a cell reference, you must concatenate the reference to an operator like ">"&E10.
• The DATE function guarantees Excel will interpret a date correctly.
• Referring to a date in another cell makes it easy to change the date without editing the formula.

Pro tip: Avoid hard-coding a date into a formula. Instead, put the date in a cell, then reference that cell in your formula. This makes the worksheet more useful since you can easily see the date being used and change the date when needed without editing the formula.

Wildcards

The COUNTIFS function supports three wildcards you can use in criteria:

• Asterisk (*) - match zero or more characters
• Question mark (?) - match any one character
• Tilde (~) - an escape character to match a literal wildcard

 For example, to count text strings that contain the text "apple", you can use a formula like this:

=COUNTIFS(range,"*apple*") // cells that contain "apple"

To count cells that contain any 3 text characters, you can use:

=COUNTIFS(range,"???") // cells that contain any 3 characters

Note that wildcards only work with text values. The formula above will not count a 3-digit number like 123 or 812. The worksheet below shows how wildcards can be used with the COUNTIFS function. The formulas in F5:F8 apply the criteria described in column E.

=COUNTIFS(B5:B11,"mi*") // begins with "mi"
=COUNTIFS(B5:B11,"*ota") // ends with "ota"
=COUNTIFS(B5:B11,"????") // contains 4 characters
=COUNTIFS(B5:B11,"*~?") // ends with "?"

Note the last formula in F8 uses "*~?" to match a question mark (?) that occurs at the end of "Montana?" and "Michigan?". The tilde (~) is an escape character that allows you to find a literal wildcard. To match a question mark (?), use "~?"; to match an asterisk(*), use "~*"; and to match a tilde (~), use "~~". The table below shows more examples of how wildcards can be used:

Note: wildcards only work with text, not numbers.

OR logic

The COUNTIFS function is designed to apply multiple conditions with "AND logic," so there is no obvious way to count cells with "OR logic". However, one workaround is to provide the criteria as an array constant like {"red","blue}, and then nest the COUNTIFS formula inside the SUM function like this:

=SUM(COUNTIFS(range,{"red","blue"})) // count red or blue

The formula above will count cells in range when cells in range contain "red" or "blue". Essentially, COUNTIFS returns two sums, one for "red" and one for "blue", and SUM returns the sum of the counts. For more details, see this example.

Summary Table

You can use COUNTIFS to create a simple summary table. In the worksheet below, we have a list of unique colors in F5:F9. The goal is to subtotal the amounts in column D by color. The formula in cell G5, copied down, is:

=COUNTIFS($C$5:$C$16,F5)

Notice that the range is locked as an absolute reference to prevent changes as the formula is copied down the column. If you are using Excel 2021 or later, you can generate all totals at once with a dynamic array formula like this:

=COUNTIFS(C5:C16,F5:F9)

We don't need the absolute references in this case because a single formula creates all results. You can go one step further by using the UNIQUE function in cell F5 to get a list of unique colors, then referring to the spill range directly like this:

=COUNTIFS(C5:C16,F5#)

In Excel 365, you can also use the new GROUPBY function to create a summary table like this.

Array Problem

Note: For more advanced use cases, this is an important limitation in what COUNTIFS can do.

One of the more tricky limitations of COUNTIFS is that it won't allow an array for a range argument. To understand the problem, consider the worksheet below, where we have 12 dates in column B and 12 amounts in column C. The goal is to create a formula to count the amounts by year. We can do that with the following COUNTIFS formula:

=COUNTIFS(B5:B16,">="&DATE(E5,1,1),B5:B16,"<="&DATE(E5,12,31))

Note: I would normally use absolute references for the two ranges so that the formula can be copied down without changes, but I have left the addresses relative here so they are easier to read.

This formula works fine, but it's a little complicated. If you have some experience with Excel formulas, you might think you can use COUNTIFS and YEAR together in a clever formula like this:

=COUNTIFS(YEAR(B5:B16),E5)

The idea is to extract the year from the dates in column B with the YEAR function, and then use 2024 (in cell E5) for the criteria. This would be cool if it worked. However, Excel won't even let you enter this formula. If you try, you'll get a generic "There's a problem with your formula" error:

The problem is that COUNTIFS requires a proper range for the range argument, but YEAR(B5:B16) will return an array like this:

{2024;2024;2024;2024;2024;2025;2025;2025;2025;2025;2025;2025}

To be clear, using YEAR like this works fine in most other formulas. However, COUNTIFS is not programmed to handle arrays, so it won't work. How can we work around this problem? One nice alternative is to switch to the SUMPRODUCT function and use a formula like this:

=SUMPRODUCT(--(range=criteria))

If we modify the pattern above to fit the workbook example, we get the following:

=SUMPRODUCT(--(YEAR(B5:B16)=E5))

This formula is quite a bit simpler than the COUNTIFS formula above. It's a good example of how SUMPRODUCT can often solve a tricky problem in a clever and elegant formula.

Remember: If you try to provide an array for a range, you won't be able to enter the formula because Excel will display a "There's a problem with your formula" error dialog. The "array problem" is not mentioned explicitly.

Limitations

The COUNTIFS function has several limitations you should be aware of:

• COUNTIFS is not case-sensitive. Use the EXACT function for case-sensitive counts.
• Conditions in COUNTIFS are joined by AND logic. All conditions must be TRUE. For simple OR logic, see the approach above, or the more advanced formula here.
• COUNTIFS requires an actual range for the range argument; you can't provide an array. This means you can't alter values in a range inside your formula before applying criteria. Read more here.
• COUNTIFS does not correctly count long numbers greater than 15 digits. Example here.
• If you reference a range in an external workbook, COUNTIFS requires that the workbook be open to calculate a result. If the external workbook is not open, you will see a #VALUE! error. As a workaround, you can switch to the SUMPRODUCT function, which does not have this limitation. The syntax will look like this: =SUMPRODUCT(--(criteria_range=criteria)). See this page for details.
• COUNTIFS has some other quirks, which are detailed in this article.

The most common way to work around most of these limitations is to use the SUMPRODUCT function. However, the latest version of Excel offers powerful alternatives, including BYROW, BYCOL, and GROUPBY.

Notes

• All ranges must be the same size or COUNTIFS will return a #VALUE! error.
• Text strings in criteria must be enclosed in double quotes  ( i.e.,">32", "TX", "app*", etc.)
• Cell references in criteria are not enclosed in quotes, i.e., "<"&A1
• To match a literal question mark(?) or asterisk (*), use a tilde (~) like (~?, ~*).
• COUNTIFS can handle up to 127 conditions.
• COUNTIFS requires a range. You can't substitute an array.
• COUNTIFS returns incorrect results when used to match strings longer than 255 characters.
• COUNTIFS will return a #VALUE error when referencing another workbook that is not open.