MAXIFS

The MAXIFS function returns the maximum value from a range of cells that meets multiple criteria. This function is handy when you want to find the highest value in a dataset based on specified conditions.

Syntax

=MAXIFS(Max_range, Criteria_range1, Criteria1, [Criteria_range2, Criteria2,...])

Max_range The range of cells from which you want to find the maximum value.
Criteria_range1 The range of cells that contain the first set of criteria.
Criteria1 The criteria that must be met in Criteria_range1.
Criteria_range2 Criteria2 ... Additional ranges and criteria sets you want to apply.
Note: You can provide up to 127 range/criteria pairs in MAXIFS.

About MAXIFS

When you need to sift through your data to identify the highest value that satisfies specific conditions, MAXIFS in Excel is your go-to tool. This function allows you to set multiple criteria to narrow down your search for the maximum value in a range of cells, offering precision in your data analysis tasks. Whether you're managing budgets, sales figures, or any other dataset, MAXIFS can help you pinpoint the top value that meets your customized conditions with ease.

Examples

Suppose you have a dataset that includes sales figures for different regions and months. You want to find the highest sales amount for the 'East' region in the month of 'January'. The MAXIFS formula would be: =MAXIFS(Sales_range, Region_range, "East", Months_range, "January"). This will return the maximum sales value that fulfills both conditions.

Imagine you have a table with student scores for various subjects and you wish to determine the highest score obtained by a student named 'Alice' in 'Math' and 'Science'. The MAXIFS formula would be: =MAXIFS(Scores_range, Names_range, "Alice", Subjects_range, "Math", Subjects_range, "Science"). This will provide you with Alice's highest score across both subjects.

Questions

What is the maximum number of range/criteria pairs that can be provided in the MAXIFS function?

You can provide up to 127 range/criteria pairs in the MAXIFS function, enabling you to apply a wide range of criteria to pinpoint the maximum value in your dataset.

Can the MAXIFS function handle non-numeric criteria for finding the maximum value?

Yes, the MAXIFS function can handle both numeric and non-numeric criteria. You can specify text, dates, logical values, or any other type of criteria to filter the dataset and find the maximum value that meets your specified conditions.

Is MAXIFS case-sensitive when evaluating criteria?

Yes, MAXIFS is case-sensitive when evaluating criteria. Ensure that your criteria match the case of the data in your dataset to obtain accurate results.

Related functions

MAX
MAXA
MINIFS
SUMIFS
COUNTIFS
AVERAGEIFS