Excel WhisperExcel Whisper

How to Use MAXIFS Function in Excel

Complete guide to Excel's powerful multi-criteria maximum function with practical examples

MAXIFS is a powerful Excel function that finds the maximum value among cells that meet multiple criteria. Whether you need to identify the highest sales figure for a specific region and product category, find the maximum test score for a particular class, or determine the peak performance metric under specific conditions, MAXIFS provides a straightforward solution. Learn through practical examples from basic conditional maximums to more advanced filtering techniques.

Try MAXIFS Online

Test MAXIFS function directly in your browser - faster and easier than Excel

Pro Mode
Try this

Drop Excel or CSV files here

Upload up to 2 files (10MB total)โ€ข
Free Plan
Upgrade to Standard for higher limits โ†’

MAXIFS Function Guide

Step-by-step instructions for using MAXIFS

Function Syntax

=MAXIFS(max_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

Steps to Use

1

Select the cell where you want the maximum value result

2

Start with =MAXIFS(

3

Select the range containing values to find the maximum from (e.g., D2:D100)

4

Select the first criteria range (e.g., A2:A100)

5

Enter the first criteria (e.g., "East" or ">1000")

6

Add more criteria ranges and conditions as needed (each in pairs)

7

Press Enter to complete the formula

Use Cases

Sales Analysis

Find highest sales figures by region, product category, or time period

Performance Tracking

Identify peak performance metrics under specific conditions

Inventory Management

Determine maximum stock levels for specific product categories

Tips & Notes

  • 1Max range and all criteria ranges must be the same size (rows and columns)
  • 2Text criteria must be enclosed in double quotes (e.g., "East")
  • 3Numeric criteria can use comparison operators (e.g., ">5000", "<=100")
  • 4Date criteria should use the proper format (e.g., ">="&DATE(2023,1,1))
  • 5You can use wildcards (* for any sequence of characters, ? for any single character) in text criteria
  • 6Criteria are combined with AND logic (all conditions must be met)
  • 7Up to 126 criteria pairs (range and condition) can be used
  • 8MAXIFS ignores text values, logical values, and empty cells in the max_range
  • 9For OR logic, use multiple MAXIFS formulas with MAX function: =MAX(MAXIFS(...), MAXIFS(...))

Frequently Asked Questions about MAXIFS

Common questions and solutions for Excel MAXIFS function

MAX finds the maximum value in a range without any conditions. MAXIF doesn't exist as a native Excel function (you'd need to use MAX and IF together). MAXIFS finds the maximum value that meets multiple criteria. MAXIFS is more powerful for complex data analysis where you need to find maximum values that satisfy several conditions simultaneously.

This typically happens for several reasons: 1) None of your data meets all the specified criteria, 2) The max_range contains text or logical values that MAXIFS ignores, 3) There are hidden spaces or formatting issues in your criteria, or 4) The ranges in your formula are not the same size. Double-check your criteria and ensure all ranges have the same dimensions.

MAXIFS uses AND logic by default (all criteria must be met). For OR logic (where any criterion can be met), use multiple MAXIFS formulas with the MAX function: =MAX(MAXIFS(max_range, criteria_range1, criteria1), MAXIFS(max_range, criteria_range2, criteria2)). This returns the maximum value that meets either of the criteria.

Yes, MAXIFS supports wildcards: * (matches any sequence of characters) and ? (matches any single character). For example, =MAXIFS(max_range, text_range, "*Smith*") will find the maximum value where the corresponding text contains 'Smith' anywhere in the cell. This is useful for partial text matching.

For date ranges, use comparison operators: =MAXIFS(max_range, date_range, ">="&start_date, date_range, "<="&end_date). This finds the maximum value where dates fall between start_date and end_date (inclusive). Make sure your date criteria are formatted as dates or use the DATE function to create date values.

MAXIFS only returns the maximum value. To find the second or third highest value that meets your criteria, you can combine MAXIFS with other functions like LARGE and FILTER (in newer Excel versions), or use more complex array formulas. For example, to find the second highest value, you could use a formula that first excludes the maximum value and then finds the maximum of the remaining values.

MAXIFS only returns the maximum value, not its location. To find both the value and its corresponding information, you can combine MAXIFS with INDEX and MATCH functions, or in newer Excel versions, use XLOOKUP or FILTER functions. This allows you to first find the maximum value and then locate the row containing that value to extract additional information.

MAXIFS was introduced in Excel 2016 as part of Office 365. It's available in Excel 2016 and later versions for Windows, Excel 2016 and later for Mac, Excel for the web, and Excel for mobile devices. For older versions, you can achieve similar functionality using array formulas combining MAX and IF functions, though these are more complex to create and maintain.