If you need to quickly find the maximum value in a range of data based on certain criteria, the MAXIFS function will come in handy.
In this tutorial, I will show you how to use the MAXIFS function in Google Sheets.
The MAXIFS function will return the maximum value in a range filtered by the criteria that you specify in your formula.
The syntax of MAXIFS is:
MAXIFS(range, criteria_range1, criterion1, [criteria_range2, criterion2, …])
- range – the range of cells that you want to return the maximum value for
- criteria_range1 – the range of cells to evaluate criterion1 for
- criterion1 – the condition or test to apply to criteria_range1
- criteria_range2 – This is an optional argument. You can continue to add additional criteria ranges here
- criterion2 – if you use additional criteria ranges, this will be the condition or test to apply to your additional ranges
There are a couple of important notes you should consider when using MAXIFS:
- the function will return 0 if none of the criterion are satisfied
- the range and criterion ranges have to be the same size, otherwise, the function will return a #VALUE error
Using the MAXIFS Function
Now that we have covered the basics of the syntax, let’s take a look at how to actually use this function.
Here is an example:
In the above example. I have a dataset that contains the sales for various products and the department the product belongs to. I want to use the MAXIFS function to return the clothing product that has the most sales revenue.
The formula I would use to do this is:
Let’s break down each parameter of this formula so we can understand what this function is doing:
- C2:C11 – this is the range that I want to return the maximum value for
- A2:A11 – this is the range that my criteria will be applied to
- “Clothing” – this is my criteria. I only want to return values where column A2:A11 is equal to “Clothing”
So the function returns 1004 because that contains the maximum value for data where column A is equal to “Clothing”
Now let’s take a look at an example where I want to use MAXIFS with multiple criteria.
Here is the data I will be using:
I will be setting up a formula that will return the maximum value in the clothing department where the month is equal to April.
This is the formula I will be using to do this:
As you can see the formula is very similar to the previous one, but this time I have added a second criteria range (A2:A10) and a second criterion (“April”).
The MAXIFS function is the best way to return the maximum value in a set of data based on certain criteria.
I have only covered the basics of how to use this function, however as you get more advanced with it, you can combine it with other functions, and place various comparison operators in your criterion which will create a more powerful formula.