MAXIFS

• How to use MAXIFS formula?
• Examples of using MAXIFS formula
• MAXIFS formula not working?
• Similar formulas to MAXIFS

Usage

Use the MAXIFS formula with the syntax shown below, it has 3 required parameters and 2 optional parameters:

=MAXIFS(range, criteria_range1, criterion1, [criteria_range2, criterion2], ...)

1. range (required):
   The range of cells to evaluate for the maximum value.
2. criteria_range1 (required):
   The range of cells to evaluate for the first criterion.
3. 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.
4. criteria_range2 (optional):
   An optional range of cells to evaluate for an additional criterion.
5. 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.

Examples

Find the highest sales amount for a specific product

Using 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.

Common Mistakes

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.

Missing arguments

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.

Related Formulas

The following functions are similar to MAXIFS or are often used with it in a formula:

• MAX

  The MAX function 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.

• MINIFS

  The MINIFS function 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.

• SUMIFS

  The SUMIFS formula 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.

• AVERAGEIFS

  The AVERAGEIFS formula 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).

Learn More

You can learn more about the MAXIFS Google Sheets function on Google Support.