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

Hello everyone! Today i will show you how to use MAX, MAXIFS, and LARGE functions in Microsoft Excel step by step. Very easy and useful formulas for finding the highest value, conditional highest value, or Top 1, Top 2, Top 3 ranking numbers in your table! MAXIFS function is available in Excel 2019, Excel 2021, Excel 2024, Excel 365, and Excel for Web.

1. Summary Difference: MAX vs MAXIFS vs LARGE

• MAX: Finds the overall highest (1st place) value in a range.
• MAXIFS: Finds the highest value with 1 or more criteria/conditions.
• LARGE: Finds the k-th largest value (e.g. 1st highest, 2nd highest, 3rd highest).

Think of them like sports awards:

• MAX(range) = Top 1 Gold Champion (#1).
• MAXIFS(range, criteria_range, criteria) = Champion inside "Class A".
• LARGE(range, 2) = Silver Medal runner-up (#2). LARGE(range, 3) = Bronze Medal (#3).

2. 🛠️ Formula Syntax & Parameters

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

• max_range / array: Required. The range of numbers to analyze (e.g., C2:C7).
• criteria_range1 & criteria1: Required for MAXIFS. Range to test and condition to match.
• k: Required for LARGE. The position/rank from highest to lowest (e.g., 1 for 1st largest, 2 for 2nd largest, 3 for 3rd largest).

3. 📋 Practical Examples

Example Data Table (A2:C7)

Example 1: Find Overall Highest Value with MAX

To find the highest overall score in the entire table:

=MAX(C2:C7)

👉 Result: 95 (Emma's score).

Example 2: Find Highest Score in Specific Department with MAXIFS

To find the highest score in "Sales" department only:

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

👉 Result: 92 (Alex's score in Sales).

Example 3: Get Top 1, Top 2, Top 3 Highest Scores with LARGE

If you want to find the 2nd highest score across all students:

• 1st Highest (k=1): =LARGE(C2:C7, 1) 👉 95
• 2nd Highest (k=2): =LARGE(C2:C7, 2) 👉 92
• 3rd Highest (k=3): =LARGE(C2:C7, 3) 👉 88

=LARGE(C2:C7, 2)

👉 Result: 92 (2nd largest score).

Example 4: MIN vs SMALL & MAX vs LARGE Comparison

Check out how MIN and SMALL work similarly for finding lowest / Nth smallest values!

💡 Quick Tips for MAX, MAXIFS & LARGE

1. MAX(range) is identical to LARGE(range, 1): =MAX(C2:C7) gives exact same result as =LARGE(C2:C7, 1).
2. LARGE returns #NUM! if k > count: If you ask for 10th largest in a range of only 6 numbers (=LARGE(C2:C7, 10)), Excel returns #NUM! error!
3. Use =LARGE(C2:C7, {1,2,3}) with Dynamic Array: In Excel 365 / 2024, passing array {1,2,3} into LARGE spills Top 3 ranks into 3 cells automatically!
4. max_range must be first in MAXIFS: Always put the range of numbers you want to calculate at the start of MAXIFS.

📚 Related Resources & Tutorials

• How to Use MIN vs MAX and SMALL vs LARGE Function in Excel
• MAX vs MAXIFS Function in Excel
• Top 15 Basic Excel Formulas for Beginners ↗