MAXIFS(max_range, criteria_range1, criteria1 [,criteria_range2, criteria2] [..])

Returns the largest value in a list or array of numbers that satisfies multiple conditions.

max_rangeThe actual cells to max.
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

* Added in Excel 2019.
* This function includes hidden rows.
* This function uses the actual cell values and not the displayed formatted values.
* This function supports wildcards (? or *).
* You can have up to 127 criteria.
* If "max_range" and "criteria_range" are not the same size and shape, then #VALUE! is returned.
* If the function is not available, then #NAME? is returned.
* You can use the MAX function to return the largest value with no conditions.
* You can use the MAXA function to include logical values and text.
* You can use the MAXIF - User Defined Function if you want to satisfy one condition.
* This is similar to the AVERAGEIFS function.
* This is similar to the COUNTIFS function.
* This is similar to the MINIFS function.
* This is similar to the SUMIFS function.
* For an illustrated example refer to the page under Advanced Functions.
* For the Microsoft documentation refer to support.office.com

 ABC
1=MAXIFS(C1:C5,B1:B5,"Barry") = 30Barry30
2=MAXIFS(C1:C5,B1:B5,"=Barry") = 30Simon40
3=MAXIFS(C1:C5,B1:B5,"=Simon") = 40Barry20
4=MAXIFS(C1:C5,B1:B5,"Barry",C1:C5,"<>20") = 30James60
5=MAXIFS(C1:C5,B1:B5,"Barry",C1:C5,"=20") = 20David80
6=MAXIFS(C1:C5,B1:B5,"Barry",C1:C5,">"&C3) = 30  

1 - What is the largest value in the range "C1:C5" that have "Barry" in the range "B1:B5".
2 - What is the largest value in the range "C1:C5" 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 largest value in the range "C1:C5" that have "Simon" in the range "B1:B5".
4 - What is the largest value in the range "C1:C5" 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 largest value in the range "C1:C5" that have "Barry" in the range "B1:B5" and have a value equal to 20 in the range "C1:C5".
6 - What is the largest value in the range "C1:C5" that have "Barry" in the range "B1:B5" and have a value greater than the value in the cell "C3" in the range "C1:C5".

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