The advantage of Google Sheets MAXIFS function is its multiple conditions filtering capability unlike MAX or LARGE. There are alternative functions in Google Sheets that can give you the same result. For example you can use FILTER or QUERY instead of MAXIFS and both of them have an edge over MAXIFS. I’ll come to that.
When you want to return the maximum value in a range of cells that is filtered by a set of criteria you can depend Google Sheets MAXIFS Function. On the other hand when you want to return the maximum value along with any other value from the same row, better to use FILTER or QUERY or even a Vlookup can be a better option.
Here we can learn how to use MAXIFS formula in Google Sheets. Further you can learn the use of “<, >, <>” comparison operators within MAXIFS. The use of comparison operators with text, data and numeric may vary in Google Sheets MAXIFS Function.
How to Use Google Sheets MAXIFS Function
MAXIFS(range, criteria_range1, criterion1, [criteria_range2, criterion2, …])
I’ve imported a large table from Wikipedia that containing the list of multiple Olympic gold medallists from all over the world. I’ve taken the advantage of Google Sheets IMPORTHTML function to import this data for our MAXIFS tutorial. See that IMPORTHTML function and the top part of the sample sheet. For our Google Sheets Maxifs function example, this part of data is enough. Further, on your sheet, you can apply the below import function as it is.
Now let’s apply MAXIFS on this.
This formula would return the value 4, which is the number of most gold medals won by any individual from France in the Olympics so far. In the below maxifs formula I’ve only changed the country from France to United States.
Formula: =maxifs(H4:H,C4:C,”United States”)
Additional Information (you can skip this)
Now when you want to return who has won the most Olympic Gold medals in the history of Olympics, that from France, the MAXIFS function is helpless. You may want to use a Query formula as below.
=query(A4:K,”Select B,C,H where C=’France’ order by C desc limit 1″)
When there are multiple people with same number of medals, then you may want to change the last part of the formula from limit 1 to limit 2 or 3.
More Examples to the Practical Usage of MAXIFS Function in Google Sheets
Now I’m going to use comparison operators in Google Sheets MAXIFS Function. I’ve skipped this steps in my earlier MINIFS tutorial. So you can adopt the same there also.
Find the number of most gold medals won by any individual other than from the U.S.
Now one more example.
Ms. Larisa Latynina from former Soviet Union won 9 gold medals in Olympics and she is the one and only lady who tops the medal tally with most number of Gold medals.
The above formula skips the country Soviet Union and returns the next maximum number of gold medals and it’s 8. As already told you can’t return the name.
This way you can use multiple criteria in MAXIFS in Google Sheets. Regarding the use of comparison operators, it’s different from text, when use with numeric values.
It should be like “>150” or “<>150”. That’s all for now. Enjoy!