Sample Usage
MAXIFS(B2:B20,A2:A20,TRUE)
MAXIFS(C2:C20,A2:A20,"Student",B2:B20,G1)
Syntax
MAXIFS(range, criteria_range1, criteria1, [criteria_range2,...], [criteria2,...])
- range - the range to evaluate to find the max value.
- criteria_range1 - the range to check against criteria1.
- criteria1 - the pattern or test to apply to criteria_range1.
- criteria_range2 - [OPTIONAL] the range to check against criteria2.
- criteria2 - [OPTIONAL] the pattern or test to apply to criteria_range2.
- repeatable
Examples
MAXIFS(B2:B20,A2:A20,TRUE) returns the max value from the range B2:B20 where the corresponding value in the range A2:A20 is TRUE.
MAXIFS(C2:C20,A2:A20,"Student",B2:B20,G1) returns the max value from the range C2:C20 where the corresponding value in the range A2:A20 is "Student" AND where the corresponding value in the range B2:B20 equals the value in G1.
Notes
- Cells in a range that contain TRUE or FALSE evaluate to 1 or 0 respectively.
- Ensure that values for criteria1, criteria2,.. are in quotation marks.
- The range, criteria_range1, criteria_range2,.. must contain the same number of rows and columns.
- criteria can contain wildcard characters. Currently, the use of asterisks (*) is supported.
- An asterisk matches any sequence of characters.
- To match an actual asterisk, type a tilde (~) before the character. For example: (~*).
- You can use logical operations (like >,<,=,<>, etc.) as criteria in quotation marks. For instance, ">1"