COUNTIFS

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

Returns the number of numerical values in a list, table or cell range that satisfies multiple conditions.

criteria_range1The range of cells you want evaluated.
criteria1The 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, text and errors.
* You can use the COUNTVISIBLEIFS - User Defined Function to exclude hidden cells.
* You can use the DCOUNT function to return the number of non blank cells in a column that satisfies multiple conditions.
* 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

 ABC
1=COUNTIFS(B1:B5, "Jan") = 2Jan30
2=COUNTIFS(B1:B5, "=Jan") = 2Feb40
3=COUNTIFS(B1:B5, "=Feb") = 2Jan20
4=COUNTIFS(B1:B5, "Jan", C1:C5, "<>20") = 1Feb60
5=COUNTIFS(B1:B5, "Jan", C1:C5, "=20") = 1Mar80
6=COUNTIFS(B1:B5, "Jan", C1:C5, ">" & C3) = 1  

1 - What is the number of cells that have "Jan" in the range "B1:B5".
2 - What is the number of cells that have "Jan" 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 "Feb" in the range "B1:B5".
4 - What is the number of cells that have "Jan" 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 "Jan" 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 "Jan" in the range "B1:B5" and have a value greater than the value in cell "C3" in the range "C1:C5".

© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited Top