𝗜𝗻𝘁𝗲𝗿𝘃𝗶𝗲𝘄𝗲𝗿:
You have 2 minutes to solve this Excel problem.

You have the following data:

Employee Department Salary

John IT 75,000
Sarah HR 60,000
Mike IT 82,000
David Finance 90,000
Alice HR 65,000

How would you calculate the highest salary in each department?

𝗠𝗲: Challenge accepted! 💪

For Excel 365 / Excel 2021:

=MAXIFS(C2:C6,B2:B6,E2)

(Assume cell E2 contains the department name, such as IT.)

💡 Explanation:

The MAXIFS() function returns the maximum value that meets one or more conditions.

C2:C6 is the salary range.

B2:B6 is the department range.

E2 contains the department to search for.

Excel returns the highest salary for the selected department.


This challenge tests your understanding of: ✅ MAXIFS()
✅ Conditional Functions
✅ Data Analysis

🎯 Expected Output Example

Department Highest Salary

IT 82,000
HR 65,000
Finance 90,000


🚀 Bonus (For Older Excel Versions)

=MAX(IF(B2:B6=E2,C2:C6))

Note: In older Excel versions, confirm this as an array formula by pressing Ctrl + Shift + Enter instead of just Enter.

🚀 Tip for Excel Job Seekers:

The MAXIFS() and MINIFS() functions are frequently used in business reporting. Make sure you also practice:

SUMIFS()

COUNTIFS()

AVERAGEIFS()

MAXIFS()

MINIFS()


These are among the most commonly tested Excel functions in interviews and are essential for real-world reporting and dashboard creation.

❤️ React with ❤️ for more Excel interview challenges!