Count reference cells based on multiple criteria.
The Excel COUNTIFS function returns a count of numbers based on one or multiple given criteria. COUNTIFS can be used to count values when selected cells meet criteria based on dates, numbers, and text.
=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)
range1 – The first range to evaluate containing criteria1
criteria1 – The criteria to apply for addition in range1
range2 [optional] – The second range to evaluate containing criteria2
criteria2 [optional] – The criteria to apply for addition in range2
Count of values based on multiple criteria
- The COUNTIFS function is a plural variation of the COUNTIF function as it accepts multiple arguments to count one or multiple ranges or array of numbers.
- The function can handle between 1 to 127 range or criterion pairs.
- For text criteria that include logical operators (>,<,<>,=) or wildcards symbols (*,?) for partial matches must be enclosed in double quotation marks (“) like “<30”.
- The user can find a literal question mark or asterisk by using a tilde (~) symbol before a question mark or asterisk (i.e., ~?, ~*). A criteria a*a will match with all cells containing a string beginning with “a” and ending in “a” like a text “abide” or “abate”.