ElyxAI
formulas

How to Use MAXIFS Function

Excel 2016Excel 2019Excel 365

Learn to use MAXIFS to find the maximum value in a range based on multiple criteria. This function returns the largest number that meets all your specified conditions, ideal for analyzing datasets where you need conditional maximum values like highest sales by region or best performance in a category.

Why This Matters

MAXIFS saves time analyzing large datasets by automatically finding conditional maximums without manual filtering or sorting. It's essential for business intelligence, sales analysis, and performance reporting.

Prerequisites

  • •Basic understanding of Excel functions and cell references
  • •Familiarity with comparison operators (=, >, <, etc.)
  • •Knowledge of range selection and data organization

Step-by-Step Instructions

1

Prepare Your Data

Organize data in columns with headers; ensure the maximum value column and criteria columns are clearly defined with no blank cells in your ranges.

2

Click on Target Cell

Select the cell where you want the result to appear, typically in a summary area below or beside your data.

3

Enter MAXIFS Formula

Type =MAXIFS(max_range, criteria_range1, criterion1, [criteria_range2, criterion2], ...) replacing placeholders with your actual cell ranges and conditions.

4

Define Your Criteria

Specify one or more criteria: for example, =MAXIFS(D:D, A:A, "Sales", B:B, ">10000") finds the maximum in column D where column A equals "Sales" and column B is greater than 10000.

5

Press Enter and Verify

Press Enter to execute the formula; verify the result matches your expected maximum by manually checking your data against the criteria applied.

Alternative Methods

Using Array Formulas with MAX and IF

Use =MAX(IF(criteria, value_range)) as an array formula (Ctrl+Shift+Enter) for older Excel versions that don't support MAXIFS.

Combining FILTER and MAX

In Excel 365, use =MAX(FILTER(max_range, criteria_range=criterion)) for dynamic, flexible filtering with conditional maximum.

Tips & Tricks

  • ✓Use absolute references ($ signs) when copying formulas across cells to prevent range shifts.
  • ✓Test with a small dataset first to ensure your criteria logic is correct before applying to large datasets.
  • ✓Use wildcards like "*" in criteria to match partial text values (e.g., "Sales*" matches "Sales_North").

Pro Tips

  • ★Combine MAXIFS with other functions like MATCH or INDEX to return entire rows matching maximum conditions.
  • ★Use named ranges in MAXIFS for cleaner, more readable formulas that are easier to maintain.
  • ★Stack multiple MAXIFS formulas in a helper column to rank all matching values, not just the maximum.

Troubleshooting

Formula returns #NAME? error

Ensure MAXIFS is available in your Excel version (2016+); if using older Excel, switch to array formulas with MAX and IF instead.

Formula returns 0 or unexpected low value

Check that your criteria match actual data values exactly (case-sensitive for some comparisons); use wildcards or adjust comparison operators.

Formula ignores some matching records

Verify no blank cells exist in your ranges and criteria are logically correct; use the AND logic carefully with multiple criteria.

Related Excel Formulas

MINIFSSUMIFSCOUNTIFSAVERAGEIFSMAXIF

Frequently Asked Questions

Can MAXIFS work with text criteria?
Yes, MAXIFS works with text criteria using exact matches or wildcards. For example, "Sales" finds exact matches or "Sale*" matches partial text like "Sales_North".
How many criteria can MAXIFS handle?
MAXIFS supports up to 127 criteria pairs in modern Excel versions, allowing complex multi-condition analysis.
What's the difference between MAXIFS and MAX with IF?
MAXIFS is cleaner and doesn't require array formula entry (Ctrl+Shift+Enter); it's also faster on large datasets and more intuitive for multiple criteria.

This was one task. ElyxAI handles hundreds.

Sign up