MAXIFS Function: Complete Guide to Finding Maximum Values with Multiple Conditions
=MAXIFS(max_range, criteria_range1, criteria1, ...)The MAXIFS function is a powerful Excel tool that allows you to find the maximum value in a range based on one or more criteria. Unlike the basic MAX function which returns the highest value regardless of conditions, MAXIFS enables you to apply multiple logical conditions to filter your data before determining the maximum. This intermediate-level function is particularly valuable in business analytics, financial reporting, and data analysis scenarios where you need to identify peak values within specific subsets of data. Introduced in Excel 2016 and available in Excel 2019 and Microsoft 365, MAXIFS complements other conditional aggregate functions like SUMIFS, AVERAGEIFS, and MINIFS. Whether you're analyzing sales performance by region, tracking maximum inventory levels by product category, or finding the highest customer order value within a date range, MAXIFS provides an elegant solution without requiring complex array formulas or helper columns. Understanding this function will significantly enhance your data analysis capabilities and streamline your spreadsheet workflows.
Syntax & Parameters
The MAXIFS function follows this syntax: =MAXIFS(max_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...). The max_range parameter is required and specifies the range containing the values from which you want to find the maximum. This range can contain numbers, dates, or text values that can be compared numerically. The criteria_range1 is the first range to evaluate against your criteria, and criteria1 is the corresponding condition. You can add multiple criteria pairs to create complex filters—Excel supports up to 127 criteria_range/criteria pairs, allowing sophisticated multi-condition analysis. Each criteria can be a specific value (like 'Sales'), a comparison operator combined with a value (like '>5000' or '<=100'), a cell reference, or a wildcard pattern for text matching. When multiple criteria are specified, MAXIFS applies AND logic, meaning all conditions must be true simultaneously for a row to be considered. The function ignores empty cells in the max_range and returns zero if no values meet the criteria. Understanding the interaction between ranges is crucial—all ranges must have the same number of rows, though they can be in different columns or even different sheets, referenced with proper syntax like Sheet2!A:A.
max_rangecriteria_range1criteria1Practical Examples
Finding Maximum Sales by Region
=MAXIFS(B:B, A:A, "North")This formula searches column B (sales amounts) and returns the maximum value where column A (region) equals 'North'. It's ideal for quick regional performance analysis without filtering the dataset.
Maximum Order Value for Premium Customers
=MAXIFS(C:C, B:B, "Premium", D:D, ">=2024-01-01", D:D, "<=2024-12-31")This formula combines three conditions: customer tier must be 'Premium' AND the order date must fall within 2024. It returns the maximum order value meeting all criteria, useful for VIP customer analysis.
Highest Temperature Reading by Weather Station
=MAXIFS(E:E, A:A, "Station_5", B:B, ">6", B:B, "<9")This formula finds the maximum temperature (column E) where the station ID (column A) is 'Station_5' AND the month number (column B) is between 6 and 9 (June-September). Multiple numeric comparisons enable precise temporal filtering.
Key Takeaways
- MAXIFS is the primary function for finding maximum values with multiple conditions in Excel 2016+, replacing complex array formulas
- The function applies AND logic to all criteria—all conditions must be simultaneously true for a row to be included in the maximum calculation
- Text criteria require proper quoting and support wildcards (* and ?) for flexible pattern matching in product codes, regions, and categories
- Performance improves significantly when using specific range references (A1:A1000) instead of entire columns (A:A), especially in large workbooks
- Combine MAXIFS with IFERROR, INDEX-MATCH, and COUNTIFS to create robust, production-ready formulas that handle edge cases and provide meaningful business insights
Pro Tips
Use absolute references for source ranges ($A$1:$A$100) but relative references for criteria to enable formula copying across rows without breaking references.
Impact : Prevents accidental range shifts when copying formulas while allowing criteria to adjust appropriately, reducing formula maintenance errors by up to 80%.
Combine MAXIFS with IFERROR to handle cases where no data meets your criteria: =IFERROR(MAXIFS(...), "No matching data") instead of returning 0.
Impact : Improves data interpretation by distinguishing between 'no data found' and 'maximum value is zero', critical for accurate business reporting and analysis.
For performance optimization with large datasets, specify exact ranges (A1:A10000) rather than entire columns (A:A). This reduces calculation time significantly, especially with multiple criteria.
Impact : Can improve formula recalculation speed by 50-70% in workbooks with thousands of formulas, making spreadsheets more responsive and professional.
Use named ranges for frequently used criteria ranges to make formulas more readable and maintainable: =MAXIFS(SalesAmount, Region, "North", Tier, "Premium").
Impact : Enhances formula clarity, reduces errors during formula editing, and makes spreadsheets easier for colleagues to understand and modify.
Useful Combinations
MAXIFS with MINIFS for Range Analysis
=MAXIFS(B:B, A:A, "North") - MINIFS(B:B, A:A, "North")Calculate the spread or range of values (difference between highest and lowest) for a specific category. This reveals data volatility and is useful for understanding performance consistency by region or product type.
MAXIFS with INDEX-MATCH for Returning Associated Data
=INDEX(D:D, MATCH(MAXIFS(B:B, A:A, "North"), B:B, 0))Find not just the maximum value but also return associated information (like product name, date, or salesperson). When MAXIFS returns the max sales amount, INDEX-MATCH retrieves the corresponding customer or transaction ID from another column.
MAXIFS with COUNTIFS for Conditional Analysis
=IF(COUNTIFS(A:A, "North", B:B, ">1000") > 0, MAXIFS(B:B, A:A, "North", B:B, ">1000"), "No data")Combine MAXIFS with COUNTIFS to verify data exists before calculating the maximum, preventing zero or error results. This creates robust formulas that handle edge cases gracefully and provide meaningful feedback.
Common Errors
Cause: The max_range contains text values that cannot be compared numerically, or criteria syntax is incorrect (e.g., missing quotes around text criteria).
Solution: Ensure max_range contains numeric values or dates. Verify criteria are properly quoted: use "North" not North. Check that date criteria use proper DATE function or recognized date format.
Cause: One or more range references are invalid, deleted, or point to a non-existent sheet. This commonly occurs when copying formulas across sheets or when source data is removed.
Solution: Verify all range references exist and are correctly spelled. Use absolute references ($A$1:$A$100) for source data to prevent reference shifts. Check sheet names in cross-sheet references (Sheet2!A:A syntax).
Cause: No values in max_range meet all specified criteria, or the criteria_ranges and max_range have mismatched row counts.
Solution: Verify criteria logic using COUNTIFS to check how many rows meet your conditions. Ensure all ranges have equal row counts. Test individual criteria separately to isolate the problematic condition.
Troubleshooting Checklist
- 1.Verify all ranges have equal row counts—mismatched dimensions are the leading cause of MAXIFS failures
- 2.Confirm text criteria are properly quoted ("North" not North) and check for leading/trailing spaces that prevent matches
- 3.Test individual criteria using COUNTIFS to isolate which condition is failing to return expected results
- 4.Ensure max_range contains numeric values or dates; text-only ranges will return errors or zero
- 5.Check for circular references if MAXIFS is referencing its own cell or dependent cells, causing calculation loops
- 6.Verify Excel version is 2016 or later; MAXIFS is unavailable in Excel 2013 and earlier—use array formulas instead
Edge Cases
max_range contains both positive and negative numbers, and no rows meet criteria
Behavior: Returns 0, not an error, making it indistinguishable from a legitimate maximum value of zero
Solution: Wrap with IFERROR or COUNTIFS verification: =IFERROR(MAXIFS(...), "No data") or =IF(COUNTIFS(...)>0, MAXIFS(...), "No data")
This is a design choice—MAXIFS treats 'no matching data' as zero rather than an error state
Criteria range contains dates, but criteria is entered as text ("2024-01-15" instead of DATE(2024,1,15))
Behavior: May return unexpected results or zero if date formatting doesn't match, as text '2024-01-15' won't match date serial numbers
Solution: Always use DATE() function for date criteria or ensure text dates match the system locale format; use >="&DATE(2024,1,1) syntax
Date handling varies by locale and Excel version; explicit DATE functions prevent region-specific issues
Multiple rows contain identical maximum values meeting the criteria
Behavior: MAXIFS returns the single maximum value (not multiple rows), even if 5 different transactions have that same highest amount
Solution: Use INDEX-MATCH or FILTER to retrieve all rows with the maximum value, or use COUNTIFS to count how many rows share the max
MAXIFS is designed to return a single scalar value; for multiple matching rows, combine with other functions or use Power Query
Limitations
- •MAXIFS returns only a single scalar value and cannot return multiple rows with identical maximum values—use INDEX-MATCH combinations or FILTER function for row-level results
- •The function applies AND logic exclusively; OR conditions require nested MAXIFS calls or array formulas, adding complexity to multi-condition scenarios
- •Performance degrades with very large datasets (1M+ rows) when using entire column references (A:A)—specific ranges (A1:A1000000) are necessary for acceptable calculation speed
- •MAXIFS is unavailable in Excel versions before 2016 and requires workarounds using array formulas in older environments, limiting compatibility in legacy systems
Alternatives
Can ignore errors and hidden rows, offering more control over which values are included. Supports 19 different aggregate functions beyond MAX.
When: When you need to exclude error values or hidden data from your maximum calculation, or when working with filtered datasets where you want to respect visibility settings.
Provides visual analysis, easier to modify criteria, and better performance with very large datasets (100K+ rows). Enables multiple analyses simultaneously.
When: When you need repeated analysis with changing criteria, exploratory data analysis, or when working with millions of rows where formula performance becomes critical.
Compatibility
✓ Excel
Since Excel 2016
=MAXIFS(max_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...) - Fully supported with all features✓Google Sheets
=MAXIFS(range, criteria_range1, criterion1, [criteria_range2, criterion2], ...) - Identical syntaxFully compatible with Google Sheets; behavior and performance are equivalent to Excel
✓LibreOffice
=MAXIFS(max_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...) - Fully supported since LibreOffice 6.2