## Purpose

## Return value

## Arguments

*range1*- The first range to evaulate.*criteria1*- The criteria to use on range1.*range2*- [optional] The second range to evaluate.*criteria2*- [optional] The criteria to use on range2.

## Syntax

## How to use

To use COUNTIFS, provide a *range* that contains cells you want to count, then provide the *criteria* needed to apply the condition. Each condition requires a separate range and criteria, so the syntax for the COUNTIFS function depends on how many conditions are provided:

```
=COUNTIFS(range1,criteria1) // 1 condition
=COUNTIFS(range1,criteria1,range2,criteria2) // 2 conditions
```

For example, in the worksheet shown, to count cells equal to "Red" in the range C5:C16, you can use COUNTIFS like this:

`=COUNTIFS(C5:C16,"red") // returns 7`

COUNTIFS returns 7, since there are 7 cells in C5:C16 that contain "Red". To extend the formula to count rows where the color in C5:C16 is "Red" and the State in D5:D16 is "TX", add another *range* and *criteria*:

`=COUNTIFS(C5:C16,"red",D5:D16,"TX") // returns 3`

In this case, COUNTIFS returns 3, since there are 3 rows where the color in column C is "Red" and the corresponding State in column D is "TX". Notice that COUNTIFS is *not* case-sensitive. You can use "Red" or "red", and "TX" or "tx" for criteria with the same result. Also notice that COUNTIF joins the two conditions with AND logic: *both conditions must be TRUE to be included in the count.* COUNTIFS can count numeric values as well. To count rows where the color is "Red" and the Amount in column F is greater than 20, you can use COUNTIFS like this:

`=COUNTIFS(C5:C16,"red",F5:F16,">20") // returns 5`

To count rows where the Color is "Red", the Amount is greater than 20, and the State is "TX", add another *range* and *criteria* like this:

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

Notice that each condition is defined with a separate "pair" of *range* and *criteria* arguments. Also, notice that criteria are enclosed by double quotes (""). To summarize, keep the following in mind when using COUNTIFS:

- Each condition requires a separate
*range*and*criteria*. - 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 that conditions provided to COUNTIFS does not matter.
- Criteria are usually enclosed in double quotes ("").

*criteria*argument in COUNTIF is somewhat unique in Excel because you typically need to enclose the criteria in double quotes (""), especially when using operators. For example, instead of simply entering >100 as the criteria, you must enter ">100" in double quotes. If you don't quote values as required,

*Excel will not let you enter the formula*. See the next section for more details and examples of the syntax required to apply various criteria.

### 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:

Target | Criteria |
---|---|

Cells greater than 75 | ">75" |

Cells equal to 100 | 100 or "100" |

Cells less than or equal to 100 | "<=100" |

Cells equal to "Red" | "red" |

Cells not equal to "Red" | "<>red" |

Cells that are blank "" | "" |

Cells that are not blank |
"<>" |

Cells that begin with "X" | "x*" |

Cells less than A1 | "<"&A1 |

Cells less than today | "<"&TODAY() |

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
```

**Pro-tip**: In general, you should avoid hardcoding a date into a formula. Instead, put the date in a cell, then reference that cell in your formula. This makes the worksheet more transparent since you can easily see the date being used, and change the date when needed without editing the formula.

### 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*in order 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 is not case-sensitive. To count values based on a case-sensitive condition, you can use a formula based on the SUMPRODUCT function with the EXACT function.
- 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 is 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. (~?, ~*).