COUNTIFS Function

The COUNTIFS function is a versatile tool in Excel for counting cells that meet multiple criteria across different ranges. It is particularly useful when specific, conditional counts are required. For example, you can use COUNTIFS in the following scenarios:

• Sales analysis: Count the number of sales transactions that exceed a certain value within a specific region. For instance, count sales over $500 in the Western region.
• Attendance Tracking: Count the number of days an employee was absent or present in a particular month.
• Inventory Management: Tally items in an inventory that are below a certain stock level and belong to a specific category, like counting accessories with less than 10 units in stock.
• Survey Analysis: Count the number of survey responses that meet multiple criteria, such as respondents who are over a certain age and prefer a particular product or service.
• Academic Performance: Calculate the number of students who scored above a certain threshold in a specific subject, such as counting students who scored over 80% in Mathematics.

The COUNTIFS function counts the number of cells in a range that meet one or more conditions. Each condition is provided as a "pair" of arguments: range + criteria. As a result, the syntax for COUNTIFS varies according to the number of conditions needed:

=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. COUNTIFS can handle up to 127 separate range/criteria pairs.

Example

In the worksheet below, the data includes Date, Color, State, Quantity, and Total. Using the COUNTIFS function, we can count rows in the data that meet multiple conditions. Column H shows the criteria used inside COUNTIFS, and column I contains the resulting count:

Let's walk through the four COUNTIFS formulas entered in the range I5:I8.

Example #1 - Color is Red

To count cells equal to "Red" in the range C5:C16, the formula in cell I5 is:

=COUNTIFS(C5:C16,"red") // color is "red"

The result is 7, since 7 cells in C5:C16 contain "Red".

Example #2 - Color is Red and State is TX

To count rows where the Color in C5:C16 is "Red" and the State in D5:D16 is "TX", we need to add another condition to test if the State is TX. We do that by adding another range/criteria pair:

=COUNTIFS(C5:C16,"red",D5:D16,"TX") // color is "red" + state is "TX"

COUNTIFS returns 3 since there are three rows where the color "Red" and the State in column D is "TX". Notice that both conditions must be TRUE to be included in the count.

Example #3 - Color is Red and Quantity is over 20

COUNTIFS can count numbers as well. To count rows where the Color is "Red" and the Amount in column F is greater than 20, the formula in cell I7 looks like this:

=COUNTIFS(C5:C16,"red",F5:F16,">20") // color is "red" + qty is >20

Notice that the criteria for Amount (">20") appears in double quotes (""), even though 20 is a number. This is one of the quirks of the COUNTIFS function and other *IFS functions: numbers that appear in criteria are enclosed in double quotes ("").

Example #4 - Color is Red, Quantity is over 20, State is TX

To add another condition to check that the State is "TX", we need to add a third range/criteria pair:

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

This is the formula in cell I8. The meaning of this formula is: count rows where the color is "red" + qty is >20 + 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. Notice that each condition requires a separate range/criteria pair and there are three pairs in total. To summarize:

• Each condition requires a separate range/criteria pair.
• All range arguments must be the same size.
• To be included in the count, all conditions must be TRUE.
• Criteria can include logical operators (>,<,<>,<=,>=) as needed.
• The COUNTIFS function is not case-sensitive.
• The order in which conditions appear does not matter. 
• Criteria are enclosed in double quotes ("").

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.

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.

Value from another cell

When using a value from another cell in a condition, the cell reference must be concatenated to an operator when used. In the example below, COUNTIFS will count the values in A1:A10 that are less than the value in cell B1. Notice the less than operator (which is text) is enclosed in quotes, but ampersand (&) and cell reference are not:

=COUNTIFS(A1:A10,"<"&B1) // count cells less than  B1

Not equal to

To create "not equal to" criteria, use the "<>" operator surrounded by double quotes (""). For example, the formula below will count cells not equal to "red" in a given range:

=COUNTIFS(range,"<>red") // not "red"

Blank cells

COUNTIFS can be configured to count cells that are blank or not blank in a given range as seen below:

=COUNTIFS(range,"") // count blank
=COUNTIFS(range,"<>") // count not blank

Note: be aware that COUNTIFS treats formulas that return an empty string ("") as not blank. This can cause trouble when counting the results from other formulas. See this example for some workarounds for this problem.

Dates

The easiest way to use COUNTIFS with dates is to refer to a valid date in another cell. For example, with a valid date in cell B1, you can count dates in a given range that are greater than B1 like this:

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

Notice we must concatenate the ">" operator to the date in B1. The operator is enclosed in quotes ("") but the ampersand (&) and the cell reference are not. The safest way to hardcode a date into COUNTIFS is to use the DATE function. This guarantees Excel will interpret the date correctly. For example, to count dates less than September 1, 2020, you can use the DATE function with COUNTIFS like this:

=COUNTIFS(range,"<"&DATE(2020,9,1)) // dates less than 1-Sep-2020

Wildcards

The wildcard characters question mark (?), asterisk(*), or tilde (~) can be used in criteria. A question mark (?) matches any one character, and an asterisk (*) matches zero or more characters of any kind. 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 of any kind, you can use a formula like this:

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

Note: wildcards only work with text values. The formula above will not count a 3-digit number like 123 or 812.

The tilde (~) is an escape character to match literal wildcards. For example, to count a literal question mark (?) in cells, you can use "~?" for criteria like this:

=COUNTIF(range,"~?")

To count text strings that end in a question mark (?), you can add an asterisk (*) like this:

=COUNTIF(range,"*~?")

Similarly, you can count asterisks(*) with "~*", and count tildes (~) "~~".

OR logic

The COUNTIFS function is designed to apply multiple criteria with AND logic. This means if you try to count cells that contain "red" or "blue" in the same range, the result will be zero (0). However, to count cells with OR logic, you can use an array constant and the SUM function like this:

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

The formula above will count cells in range that contain "red" or "blue". Briefly, COUNTIFS returns two counts in an array (one for "red" and one for "blue") and the SUM function returns the sum as a final result. For more information, see this example.

Limitations

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

• Conditions in COUNTIFS are joined by AND logic. In other words, all conditions must be TRUE for a cell to be included in a count. The workaround above can be used in simple situations.
• The COUNTIFS function requires actual ranges for all range arguments; you can't use an array. This means you can't alter values that appear in a range argument before applying criteria.
• COUNTIFS does not count long numbers greater than 15 digits correctly. Example here.
• COUNTIFS is not case-sensitive. To count values based on a case-sensitive condition, you can use the SUMPRODUCT function with the EXACT function.
• 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_range1=criteria1), --(criteria_range2=criteria2), ...). See this page for details.
• COUNTIFS has some other quirks, which are detailed in this article.

The most common way to work around the limitations above is to use the SUMPRODUCT function. In the current version of Excel, another option is to use the newer BYROW and BYCOL functions.

Notes

• Multiple conditions are applied with AND logic, i.e. condition 1 AND condition 2, etc.
• All ranges must be the same size or COUNTIFS will return a #VALUE! error.
• Criteria are typically enclosed in double quotes (i.e. "<100",  ">32", "TX").
• Criteria can include the wildcard characters "?" and "*" and "~".
• To match a literal question mark(?) or asterisk (*), use a tilde (~), i.e. (~?, ~*).