MAXIFSfunction returns the maximum numeric value from a range of cells that meet one or more specified conditions. It is commonly used to filter data and find the largest value that meets specific criteria. This function is only available in Google Sheets and is part of the Statistical functions.
- How to use
- Examples of using
MAXIFSformula not working?
- Similar formulas to
MAXIFS formula with the syntax shown below, it has 3 required parameters and 2 optional parameters:
- range (required):
The range of cells to evaluate for the maximum value.
- criteria_range1 (required):
The range of cells to evaluate for the first criterion.
- criterion1 (required):
The criterion to use for the first range. This can be a value, cell reference, or expression that evaluates to true or false.
- criteria_range2 (optional):
An optional range of cells to evaluate for an additional criterion.
- criterion2 (optional):
An optional criterion to use for the additional range. This can be a value, cell reference, or expression that evaluates to true or false.
ExamplesHere are a few example use cases that explain how to use the
MAXIFSformula in Google Sheets.
Find the highest sales amount for a specific product
MAXIFS, you can find the highest sales amount for a specific product in a large dataset that includes multiple products and sales amounts. This can help identify top-selling products and inform future product development.
Find the largest value that meets multiple criteria
When working with complex datasets, you may need to find the largest value that meets multiple criteria.
MAXIFS allows you to specify multiple criterion ranges and criteria, making it easy to filter data and find the largest value that meets all specified conditions.
Find the highest score for a specific student
If you have a large dataset of student scores and want to find the highest score for a specific student,
MAXIFS can help. This function allows you to specify the student's name as a criterion and find the highest score for that student, even in a dataset with multiple students and scores.
MAXIFSnot working? Here are some common mistakes people make when using the
MAXIFSGoogle Sheets Formula:
Incorrect range references
One common mistake is referencing the wrong range in the MAXIFS formula. Double check that the range you want to evaluate is the first argument in the formula.
Another common mistake is forgetting to include all the required arguments in the MAXIFS formula. Make sure to include the range and at least one criteria range and criterion.
Using incorrect operators
When specifying criteria in the MAXIFS formula, make sure to use the correct operator. For example, using a '+' instead of a ',' to separate criteria ranges and criteria will result in an error.
Criteria range and criterion mismatch
If the criteria range and criterion do not match in data type (e.g. one is text and one is a number), the MAXIFS formula will not return the expected result. Ensure that the data types match for each criteria range and criterion.
Not using parentheses
When specifying multiple criteria ranges and criteria, make sure to enclose them in parentheses. Failure to do so will result in an error.
The following functions are similar to
MAXIFS or are often used with it in a formula:
MAXfunction returns the maximum value from a range of cells or a set of supplied values. It is commonly used to find the largest value in a dataset.
MINIFSfunction returns the minimum value from a range of cells that meet the specified criteria. It is commonly used in statistical analysis when working with large datasets.
SUMIFSformula is a function in Google Sheets that adds the values in a specified range based on multiple criteria. It is most commonly used to sum data that meets specific criteria, such as summing sales for a particular month by region or summing expenses for a specific category and date range.
AVERAGEIFSformula calculates the average of a range of cells that meet multiple criteria. It is most commonly used to find the average of a range of values that meet specific conditions. The formula takes one required argument (average_range) and any number of optional argument pairs (criteria_range, criterion).
You can learn more about the
MAXIFS Google Sheets function on Google Support.