# COUNTIFS

COUNTIFS(criteria_range1, criteria1 [,criteria_range2, criteria2] [..])

Returns the number of numerical values in a range that satisfies multiple conditions.

 criteria_range1 The range of cells you want evaluated. criteria1 The expression that contains the criteria. criteria_range2 (Optional) The range of cells you want evaluated. criteria2 (Optional) The expression that contains the criteria.

 REMARKS
 * This function was added in Excel 2007 to replace the COUNTIF function.* For an illustrated example refer to the page under Advanced Functions* This function includes hidden cells.* This function is not case sensitive when matching text strings.* This function supports wildcards (? and *).* Wildcard character: ? = a single character.* Wildcard character: * = multiple characters.* To include the actual wildcard characters use a tilde prefix (~?), (~*) and (~~).* This function uses the actual cell values and not the displayed formatted values.* The "criteria" can contain wildcard characters.* Every additional range must have the same number of rows and columns as "criteria_range1".* Any empty cells are considered equivalent to a 0 value.* You can use the COUNT function to return the number of non blank cells with no conditions.* You can use the COUNTA function to include logical values and text.* You can use the COUNTVISIBLEIFS - User Defined Function to exclude hidden cells.* This is similar to the AVERAGEIFS, MAXIFS, MINIFS and SUMIFS functions.* For the Microsoft documentation refer to support.microsoft.com* For the Google documentation refer to support.google.com

 A B C 1 =COUNTIFS(B1:B5,"Barry") = 2 Barry 30 2 =COUNTIFS(B1:B5,"=Barry") = 2 Simon 40 3 =COUNTIFS(B1:B5,"=Simon") = 1 Barry 20 4 =COUNTIFS(B1:B5,"Barry",C1:C5,"<>20") = 1 James 60 5 =COUNTIFS(B1:B5,"Barry",C1:C5,"=20") = 1 David 80 6 =COUNTIFS(B1:B5,"Barry",C1:C5,">"&C3) = 1

 1 - What is the number of cells that have "Barry" in the range "B1:B5".2 - What is the number of cells that have "Barry" in the range "B1:B5". Notice that prefixing the condition with an equal sign means the same thing.3 - What is the number of cells that have "Simon" in the range "B1:B5".4 - What is the number of cells that have "Barry" in the range "B1:B5" and have a value not equal to 20 in the range "C1:C5".5 - What is the number of cells that have "Barry" in the range "B1:B5" and have a value equal to 20 in the range "C1:C5".6 - What is the number of cells that have "Barry" in the range "B1:B5" and have a value greater than the value in cell "C3" in the range "C1:C5".