MAX vs MAXIFS Function in Excel + Tips (Video in Khmer)

Hello everyone! Today i will show you how to use MAX and MAXIFS functions in Microsoft Excel step by step. Very easy and useful formulas for finding highest number with or without conditions! MAXIFS function is available in Excel 2019, Excel 2021, Excel 2024, Excel 365, and Excel for Web.

1. What is MAX Function?

The MAX function finds the highest (maximum) number in a range of cells.

Think of MAX like looking for the champion score:

• Put range of numbers into =MAX(range).
• Excel instantly returns the largest number!

2. What is MAXIFS Function?

The MAXIFS function finds the highest number that meets one or more specific criteria or conditions.

Think of MAXIFS like MAX with a filter:

• Find highest score in "Class A" only.
• Find highest sales for "Electronics" department.

3. 🛠️ Formula Syntax & Parameters

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

• max_range: Required for MAXIFS. The actual range of numbers where you want to find the maximum value.
• criteria_range1: Required for MAXIFS. The range of cells to test against condition 1.
• criteria1: Required for MAXIFS. The condition/rule (e.g. "Sales", ">1000", A2).

4. 📋 Practical Examples

Example Data Table (A2:C7)

Example 1: Find Overall Highest Sales with MAX

To find the highest sales amount across all departments:

=MAX(C2:C7)

👉 Result: Returns $2,800 (Emma's sales).

Example 2: Find Highest Sales for Single Criteria with MAXIFS

To find the highest sales amount in "Electronics" department only:

=MAXIFS(C2:C7, A2:A7, "Electronics")

👉 Result: Returns $2,500 (Alex's sales in Electronics).

Example 3: Find Highest Value with Multiple Criteria

Find highest sales in "Clothing" department where sales amount is less than $2,000:

=MAXIFS(C2:C7, A2:A7, "Clothing", C2:C7, "<2000")

👉 Result: Returns $1,800 (Sara's sales).

💡 Quick Tips for MAX and MAXIFS Functions

1. max_range Comes First in MAXIFS: Unlike SUMIF where sum range is at the end, MAXIFS requires the calculation range (max_range) to be the VERY FIRST parameter!
2. Criteria Ranges Must Be Same Size: All criteria_range arguments must have exact same row and column size as max_range, otherwise Excel returns #VALUE! error.
3. Use Comparison Operators in Quotes: When writing text criteria or logical operations, enclose in double quotes like ">500", "<2000", or "<>0".
4. Returns 0 If No Match Found: If no rows match your criteria, MAXIFS returns 0.
5. Excel Version Requirement: MAX works in all Excel versions. MAXIFS requires Excel 2019, Excel 2021/2024, Excel 365, or Excel for Web.

📚 Related Resources & Tutorials

• COUNTIF vs COUNTIFS Function in Excel
• IF vs IFS Function in Excel
• Top 15 Basic Excel Formulas for Beginners ↗