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
Question: How would you calculate the total salary for employees who belong to the IT department and earn more than 80,000?
๐ ๐ฒ: Challenge accepted! ๐ช
=SUMIFS(C2:C6,B2:B6,"IT",C2:C6,">80000")
๐ก Explanation:
The SUMIFS() function adds values based on multiple conditions.
โข C2:C6 is the range to sum (Salary)
โข B2:B6,"IT" includes only employees from the IT department
โข C2:C6,">80000" includes only salaries greater than 80,000
Excel returns the total salary for employees meeting both conditions.
This challenge tests your understanding of:
โ SUMIFS()
โ Multiple Criteria
โ Conditional Aggregation
โ Data Analysis
๐ฏ Expected Output Example
Formula:
=SUMIFS(C2:C6,B2:B6,"IT",C2:C6,">80000") Result: 82,000
(Only Mike meets both conditions.)
๐ Bonus (Using Cell References for Dynamic Criteria)
=SUMIFS(C2:C6,B2:B6,E2,C2:C6,">"&F2)
If:
E2 = IT
F2 = 80000
The formula becomes dynamic and updates automatically when the criteria change.
๐ Tip for Excel Job Seekers:
SUMIFS() is one of the most frequently used Excel functions in reporting and dashboards. Be comfortable using it with multiple conditions such as:
Department + Salary
Region + Month
Product + Category
Employee + Performance
Mastering SUMIFS() is essential for Excel interviews and real-world business reporting.
โค๏ธ React with โค๏ธ for more Excel interview challenges!