Count cells in a range that meet multiple criteria that is specified in other ranges.

Parameter List


A range of values you want to count. For example: A1:A5.


The pattern or test to apply to range1. For example: 5, ">5", or "spreadsheet".

[optional] range2

An additional range of values you want to count. Size must be the same as range1. For example: B1:B5.

More details

You can use wildcards in criterion. To match any singular character, use ?. To match zero or more characters, use *. If you want to match an actual question mark or asterisk, prefix it with a tilde. For example: "What~?".

If criterion is a number, each cell in range is checked to see if it is equal to criterion. If you want to use operators, such as > or >=, you need to enclose your criterion in quoatation marks.


COUNTIFS(A1:A5, ">20", B1:B5, "<30") returns 5Example 1