The COUNTIFS function returns the count of the cells that meet a single or multiple criteria in the same or different ranges in both Excel & Google Sheet.
The criteria used in COUNTIFS function can be based on dates, numbers, text, and other conditions. Logical operators (>,<,<>,=) and wildcards (*,?) for partial matching are supported by COUNTIFS function.
Here is the difference between COUNTIF and COUNTIFS –
COUNTIF is designed for counting cells with a single condition in one range, whereas COUNTIFS can evaluate different conditions in the same or different ranges.
=COUNTIFS(range1, criteria1, [range2], [criteria2], ...)
- range1 (required argument) – This is the first range to evaluate with the associated criteria.
- criteria1 (required argument) – The condition to be used on range1.
- range2, criteria2, … (optional argument) – Additional ranges and their associated conditions. COUNTIFS function allows up to 127 range/criteria pairs.
In the below example we will see how the COUNTIFS function is used to get the count of each different combination of the values in each row.
We have a table containing the name of the vegetable(in column A) and it’s amount(in column B).
In column C we have used the below formula to get the count of each vegetable and amount combination in the list.
Here range1 = A:A which is column A
criteria1 = A1(this will be A2, A3 and so on for the following cells).
range2 = B:B which is column B
criteria2 = B1(this will be B2, B3 and so on for the following cells).
Here we provided 2 conditions to COUNTIFS function.
The first pair(range1-criteria1) returns a count of every value in column B.
The second pair(range2-criteria2 ) returns a count of every value in column C.
As both pairs appear in the same COUNTIFS function, they link the values in column B with those in column C, and COUNTIFS generates the count of each value in column A and column B combination that appears in the entire column A & B.
- Non-numeric criteria needs to be enclosed in double quotes but it’s not needed for numeric criteria.
- The wildcard characters ? and * can be used in criteria for partial match. A question mark (?) matches any one character and an asterisk(*) matches any sequence of characters.
- To find a literal question mark or asterisk, use a tilde (~) in front of the question mark or asterisk (i.e. ~?, ~*).
- #VALUE! error occurs if the supplied criteria arguments are text strings more than 255 characters long.
- Each additional range should include the same number of rows and columns as the range1 argument, but the ranges do not need to be adjacent to each other. If the given range arrays are not equal in length, #VALUE! error occurs.