MAXIF Function in Excel

The MAXIF function is a unique feature introduced by Modern Functions Update (MFU) that does not exist in any version of vanilla Excel. You can now run it for free on your Excel from 2007 onward, including 365 and 2024 versions.

---

Description

The MAXIF function returns the largest numeric value in a range that meets a single specified condition. It is the single-criteria counterpart to MAXIFS, similar to how SUMIF relates to SUMIFS or AVERAGEIF relates to AVERAGEIFS.

This is particularly useful for:

• Finding top performers: Get the highest sales figure for a specific product or region
• Data analysis: Find maximum values that meet a single criterion
• Quality control: Identify the largest value within a specific category
• Reporting: Create dynamic summaries based on conditions

---

Syntax

=MAXIF(maxRange, criteriaRange, criteria)

Argument	Description
maxRange	Required. The range of cells from which you want to find the maximum value.
criteriaRange	Required. The range to evaluate against the condition.
criteria	Required. The condition to apply to criteria_range.

---

Important Rules

• All ranges must be the same size.
• If no cells meet the criteria, MAXIF returns 0 (zero).
• Empty cells that meet criteria are automatically ignored.
• The function supports logical operators (>, <, <>, =) and wildcards (*, ?, ~).

---

Criteria Syntax

The MAXIF function supports logical operators and wildcards for partial matching. When using operators, they must be enclosed in double quotes.

Target	Criteria
Cells greater than 75	">75"
Cells equal to 100	100 or "100"
Cells less than or equal to 100	"<=100"
Cells equal to “Red”	"red"
Cells not equal to “Red”	"<>red"
Cells that begin with “X”	"x*"
Cells less than value in A1	"<"&A1

When using a value from another cell, the cell reference must be concatenated to the operator using the ampersand (&).

---

Examples

Basic Usage – Single Condition

If you have sales data and want to find the maximum sale amount for “USA”:

=MAXIF(E2:E100, C2:C100, "USA")

This returns the highest value in E2:E100 where the corresponding cell in C2:C100 equals “USA”.

Using Values from Other Cells

To find the maximum value in A1:A100 where cells in B1:B100 are greater than the value in C1:

=MAXIF(A1:A100, B1:B100, ">"&C1)

The concatenation (">"&C1) is required because Excel needs to evaluate the cell reference first.

Using Wildcards

To find the maximum value where cells in B1:B100 begin with “a”:

=MAXIF(A1:A100, B1:B100, "a*")

The asterisk (*) matches any number of characters.

Finding Maximum by Category

If you have products in column A and prices in column B:

=MAXIF(B2:B100, A2:A100, "Laptop")

Returns the maximum price for laptops.

---

MAXIF vs MAXIFS

Feature	MAXIF	MAXIFS
Number of criteria	1	Multiple (up to 126)
Syntax	=MAXIF(maxRange, criteriaRange, criteria)	=MAXIFS(maxRange, criteriaRange1, criteria1, ...)
Complexity	Simpler	More complex
Use case	Simple, single-condition lookups	Complex, multi-condition lookups

---

MAXIF vs MAX + IF (Array Formula)

In older Excel versions without MAXIF, you would use an array formula:

{=MAX(IF(criteria_range=criteria, max_range))}

Example: Find maximum value in A1:A100 where B1:B100 equals “X”:

{=MAX(IF(B1:B100="X", A1:A100))}

Note: This is an array formula and must be entered with Ctrl+Shift+Enter.

Advantages of MAXIF:

• ✅ Simpler and more readable syntax
• ✅ No Ctrl+Shift+Enter required
• ✅ Works natively with references

---

Practical Use Cases

Scenario	Formula
Top sales by region	=MAXIF(SalesAmount, Region, "North")
Highest score by gender	=MAXIF(Score, Gender, "F")
Max product by category	=MAXIF(Price, Category, "Laptop")
Max value with numeric criteria	=MAXIF(Value, Range, ">10")
Max value with wildcard	=MAXIF(Value, Range, "A*")
Max value excluding blanks	=MAXIF(Value, Range, "<>")

---

Common Problems

Problem	What It Means	What To Do
#NAME? error	Excel doesn’t recognize MAXIF	Install Modern Functions Update
#VALUE! error	Ranges are not the same size	Ensure all ranges are the same size
#DIV/0! error	No numeric values in range	Check the data
Returns 0	No cells meet the criteria	Check your criteria or data

---

Notes

• ✅ Fully tested – The implementation in Modern Functions Update works reliably.
• ✅ The function is fully compatible with Excel 2007, 2010, and all later versions.
• ℹ️ MAXIF is a unique MFU feature not found in any Excel version.
• ℹ️ If no cells match the criteria, MAXIF returns 0 (zero) – be careful if 0 is a valid value in your data.
• ℹ️ MAXIF is the single-criteria version of MAXIFS.

---

See Also

MAXIFS – Returns the maximum value with multiple conditions

MINIF – Returns the minimum value with conditions

AVERAGE.WEIGHTED – Averages values with different weights