1️⃣8️⃣ Understand IF vs IF Functions
This distinction is important.
IF()
Used to make a decision.
Example:
=IF(C2>=50000,"High","Low")SUMIF()
Used to calculate a sum based on a condition.
Example:
=SUMIF(B2:B100,"IT",C2:C100)COUNTIF()
Used to count records based on a condition.
Example:
=COUNTIF(B2:B100,"IT")AVERAGEIF()
Used to calculate an average based on a condition.
Example:
=AVERAGEIF(B2:B100,"IT",C2:C100)Think:
IF → Decision
SUMIF → Conditional Total
COUNTIF → Conditional Count
AVERAGEIF → Conditional Average
🧪 Practical Interview Challenge
Suppose you have:
Employee | Department | Salary
John | IT | 75,000
Sarah | HR | 60,000
Mike | IT | 82,000
David | Finance | 90,000
Alice | HR | 65,000
Your interviewer asks:
Q1. Is John earning more than ₹70,000?
=IF(C2>70000,"Yes","No")Q2. How many employees are in IT?
=COUNTIF(B2:B6,"IT")Q3. What is the total IT salary?
=SUMIF(B2:B6,"IT",C2:C6)Q4. What is the average IT salary?
=AVERAGEIF(B2:B6,"IT",C2:C6)Q5. How many IT employees earn more than ₹80,000?
=COUNTIFS(B2:B6,"IT",C2:C6,">80000")Q6. What is the total salary of IT employees earning more than ₹70,000?
=SUMIFS(C2:C6,B2:B6,"IT",C2:C6,">70000")🏆 Key Lesson
Understand the question first.
"Should I classify this record?"
→ IF()
"How much in total?"
→ SUMIF() / SUMIFS()
"How many?"
→ COUNTIF() / COUNTIFS()
"What's the average?"
→ AVERAGEIF() / AVERAGEIFS()
One condition?
→ IF version
Multiple conditions?
→ IFS version
Double Tap ❤️ For Part-5