COUNTIF Function

The COUNTIF function counts cells in a range when they meet a specific condition. COUNTIF is one of Excel's most widely used functions, and you will find it in all kinds of spreadsheets that calculate conditional counts based on dates, text, or numbers. While powerful, COUNTIF has a unique syntax that splits logical conditions into two parts, making it different from many other Excel functions. This syntax takes a little getting used to. See below for many working examples.

Key features

• Counts cells in a range when they meet a given condition
• Works with dates, text, and numbers
• Supports comparison operators (>, <, <>, =) and wildcards (*, ?)
• Available in all Excel versions

Table of Contents

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

Basic Example

The generic syntax for COUNTIF looks like this:

=COUNTIF(range,criteria)

The criteria is applied to cells in the range. When cells meet the criteria, they are added to the count. In the worksheet below, we have a small amount of sales data. We use COUNTIF in three formulas to perform these calculations: (1) count all sales over $100, (2) count all sales by Jim, and (3) count all sales in CA (California). The formulas in G5:G7 look like this:

=COUNTIF(D5:D16,">100") // count sales over 100
=COUNTIF(B5:B16,"jim") // count sales by Jim
=COUNTIF(C5:C16,"CA") // count sales in CA

Note the following about the formulas above:

• An equal sign (=) is not needed with "is equal to" criteria (i.e., use "jim" not "=jim").
• COUNTIF is not case-sensitive, so "jim", "Jim", and "JIM" will return the same results.
• Numbers must appear in quotes ("") when used with operators (i.e. ">100").

Applying Criteria

The COUNTIF function supports logical operators (>,<,<>,=) and wildcards (*,?) for partial matching. The tricky part about using the COUNTIF function is the syntax needed to apply criteria. This is because COUNTIF is in a group of eight functions that split logical criteria into two parts: range and criteria. Because of this design, operators must be enclosed in double quotes (""). The table below shows examples of the syntax needed for common criteria:

Notice the last two examples involve concatenation with the ampersand (&) character. Any time you use a value from another cell (or the result of a formula) with a logical operator like "<", you must concatenate. This is because Excel needs to evaluate cell references and formulas first before the value can used with an operator.

Criteria in another cell

A great way to use COUNTIF is 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:

=COUNTIF(range,A1)

If you need to include an operator in the criteria, you must concatenate the cell reference to the operator. For example, to count cells that are greater than A1, use a formula like this:

=COUNTIF(range,">"&A1)

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

=COUNTIF(D5:D16,">"&F5) // count if greater than G4

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:

=COUNTIF(B5:B9,"<>red") // not equal to "red"
=COUNTIF(B5:B9,"<>blue") // not equal to "blue"
=COUNTIF(B5:B9,"<>"&E7) // not equal to 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.
• COUNTIF is not case-sensitive, so "red", "RED", and "Red" will return the same result.

Blank cells

COUNTIF can count cells that are blank or not blank. 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, COUNTIF is used to count blank and not blank cells depending on whether column D contains "x" or is empty:

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

The result is that there are 2 "not paid" (blank) invoices and 3 paid invoices. To be more precise, you can use a formula like this that counts cells only when column D contains an "x":

=COUNTIF(D5:D9,"x") // equals "x"

Dates

In Excel, dates are large serial numbers, so you can use operators like <,>, <=, and >= with dates like any other number. The tricky part about using dates in COUNTIF criteria is entering the dates in a way that Excel will understand. The most reliable method is to refer to a date in another cell or use the DATE function. The worksheet below shows both methods:

=COUNTIF(B5:B9,"<"&DATE(2024,3,1))
=COUNTIF(B5:B9,">="&DATE(2024,4,1))
=COUNTIF(B5:B9,">"&E9)

Note the following:

• When using a function with an operator, you must concatenate like "<"&DATE(2014,3,1)
• When using a cell reference with an operator, you must concatenate like ">"&E9.

Wildcards

COUNTIF supports three wildcards you can use in criteria for more flexible matching:

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

The worksheet below shows how wildcards can be used with COUNTIF. The formulas in F5:F8 apply the criteria described in column E.

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

Note the last formula in F8 uses "*~?" to match a question mark (?) that occurs at the end of "Michigan?" and "Montana?". 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:

OR logic

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

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

The formula above will count cells in range that contain "red" or "blue". Essentially, COUNTIF returns two counts in an array (one for "red" and one for "blue"), and the SUM function returns the sum of the counts. This example explains this idea in more detail.

Summary Table

You can use COUNTIF to create a simple summary table of counts. In the worksheet below, we have a list of unique colors in F5:F9. The goal is to generate a count for each color. The formula in cell G5, copied down, is:

=COUNTIF($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:

=COUNTIF(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:

=COUNTIF(C5:C16,F5#)

The advantage of using the spill range is that the counts will update if the list of unique colors changes.

Array Problem

Note: this is an advanced topic, and you don't need to understand it if you are just learning about Excel formulas. For more advanced users, this is an important limitation in how COUNTIF works and what it can do.

One of the more tricky limitations of COUNTIF is that it won't allow an array for the 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 entries per year. If you have some experience with Excel formulas, you might think you can use COUNTIF and YEAR together in a clever formula like this:

=COUNTIF(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 COUNTIF 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, SUMIF is not programmed to handle arrays, so it won't work. How can we work around this problem? One nice solution is to switch to the SUMPRODUCT function and use a formula like this:

=SUMPRODUCT(--(YEAR(range)=value))

The double negative (--) is a way to convert TRUE and FALSE values to 1s and 0s. If we adapt the pattern above to the workbook example, we get the following:

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

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 to make the formula easier to read.

Another option is to switch to the COUNTIFS function and use two conditions to capture all dates in a given year with a formula like this:

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

However, this is a more complicated formula, and I prefer the SUMPRODUCT option. In fact, this is a good example of how SUMPRODUCT can often solve a tricky problem in a clever and elegant formula.

Limitations

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

• COUNTIF only supports a single condition. To count cells that meet multiple conditions, use the COUNTIFS function.
• COUNTIF is not case-sensitive. Use the EXACT function for case-sensitive counts.
• COUNTIF 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.
• COUNTIF does not correctly count long numbers greater than 15 digits. Example here.
• If you reference a range in an external workbook, COUNTIF 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.
• COUNTIF has other quirks explained 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

• COUNTIF only supports one condition. Use the COUNTIFS function for multiple conditions.
• 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
• The wildcard characters ? and * can be used in criteria. A question mark matches any one character, and an asterisk matches any sequence of characters (zero or more).
• To match a literal question mark(?) or asterisk (*), use a tilde (~) like (~?, ~*).
• COUNTIF requires a range. You can't substitute an array.
• COUNTIF returns incorrect results when used to match strings longer than 255 characters.
• COUNTIF will return a #VALUE error when referencing another workbook that is not open.