Table of Contents
Summary
The Excel COUNTIFS function counts the number of occurrences of a condition or multiple conditions within a range of cells. The COUNTIFS function is useful for quickly counting instances of data within a dataset. The primary difference between the COUNTIFS function and COUNTIF function is that the COUNTIFS function can use multiple criteria while the COUNTIF function cannot.
Formula
=COUNTIFS(criteria_range1, criteria1, [criteria_range2, …)
Arguments (inputs)
criteria_range1 = the range of data to test the criteria against
criteria1 = the criteria to test against the range of data
Only one criteria range and criteria is required, but additional criteria ranges and criteria can be added if desired.
Return value
The COUNTIFS function returns the count of cells within the range that meet the criteria(s).
COUNTIFS Example
In the example above the COUNTIFS function in cell I6 counts the number of occurrences of “Jones” (from cell H6) in the range C6:C16 (the sales Representative’s names).
=COUNTIFS(C6:C16, H6)
The result is 3, because there are three instances of Jones (one in row 6, 10, and 16).
COUNTIFS with Multiple Criteria Example
The COUNTIFS function, unlike the COUNTIF function, can handle multiple criteria. In the example above the COUNTIFS function in cell J6 counts the number of occurrences of “Jones” (from cell H6) AND prices above $300 (from cell I6), in the range C6:C16 (the sales Representative’s names) and range E6:E16 (Prices), respectively. The COUNTIFS function will only count if both criteria are true.
=COUNTIFS(C6:C16, H6, E6:E16, “>”&I6))
The result is 1, because there is only one instance of Jones where the price is >$300 (in row 6).
COUNTIFS Not Blank Example
COUNTIFS can count the number of not blank cells within a range.
To count the number of not blank cells, use the not equal to operator “<>” combined with two quotes.
=COUNTIFS(G6:G25 , “<>”&””)
In the example above, the COUNTIFS function returns 15 non blank cells.
COUNTIFS Date Range Example
In this example, the COUNTIFS function can be used to count occurrences within a date range.
The COUNTIFS function in cell O6 counts the number of occurrences of orders that occur between the dates in cells M6 and N6, 1/1/2017 and 12/31/2017, respectively. Criteria 1 is greater than or equal to the date, indicated by the “>=” and the ampersand “&” connects the logical operator to the cell reference. Similarly, Criteria 2 is less than or equal to the date indicated by the “<=”.
=COUNTIFS(C6:C25, “>=”&M6 , C6:C25 , “<=”&N6)
The result is 10, because there are 10 instances in the date range between 1/1/2017 and 12/31/2017.
Additional COUNTIFS Examples
COUNTIFS greater than
To make the COUNTIFS criteria greater than, use the greater than symbol (>) in double quotes. A value or cell reference can be used with the greater than criteria.
=COUNTIFS(E6:E16, “>300”) <– Greater than 300
=COUNTIFS(E6:E16, “>”&I6) <– Greater than value in cell I6
COUNTIFS less than
To make the COUNTIFS criteria less than, use the less than symbol (<) in double quotes. A value or cell reference can be used with the less than criteria.
=COUNTIFS(E6:E16, “<200”) <– Less than 200
=COUNTIFS(E6:E16, “<“&I6) <– Less than value in cell I6
COUNTIFS not equal to
To make the COUNTIFS criteria not equal to a value, use the less than and greater than symbols (<>) in double quotes. A value or cell reference can be used with this criteria.
=COUNTIFS(E6:E16, “<>Jones”) <– Not equal to Jones
=COUNTIFS(E6:E16, “<>”&I6) <– Not equal to value in cell I6
