Logical & Conditional Functions — Complete Guide (10 Formulas)

Excel ke decision-making formulas — IF, IFS, AND, OR, IFERROR, SWITCH, COUNTIF, SUMIF, AVERAGEIF, MAXIFS. Real-world business logic solve karo step-by-step Data Insights par.

📑 Is Part 3 Mein Aap Kya Sikhenge:

• Topic 1: IF — Basic Conditional Logic
• Topic 2: Nested IF — Multiple Conditions
• Topic 3: IFS — Modern Multi-Condition Function
• Topic 4: AND, OR, NOT — Logical Operators
• Topic 5: IFERROR / IFNA — Error Handling
• Topic 6: SWITCH — Value Matching
• Topic 7: COUNTIF / COUNTIFS — Conditional Count
• Topic 8: SUMIF / SUMIFS — Conditional Sum
• Topic 9: AVERAGEIF / AVERAGEIFS — Conditional Average
• Topic 10: MAXIFS / MINIFS — Conditional Max/Min

📋 Note: Wahi Employee Database (Part 1 wali) use karenge. 10 employees ka data — EmpID, Name, Dept, City, Salary, Join Date, Age. Saare examples usi data pe based hain.

🔍 Definition: The IF function checks whether a condition is met and returns one value if TRUE and another value if FALSE. It is the foundation of decision-making in Excel and one of the most used functions in business analysis.

🎯 Samjho Simple Bhasha Mein: IF matlab "agar yeh sach hai toh yeh do, warna woh do." Socho tumhe check karna hai — salary 60000 se zyada hai toh "High" likho, warna "Low." IF bilkul aisa hi kaam karta hai. Real life mein hum bhi aisa hi sochte hain — "agar bahar barish ho rahi hai toh umbrella lo, warna nahi."

💡 IF Syntax:

=IF(condition, value_if_true, value_if_false)

condition: Test karna hai (e.g., A1>50)
value_if_true: Agar condition sach → yeh return
value_if_false: Agar condition galat → yeh return

Comparison operators: >, <, =, >=, <=, <> (not equal)

💻 Real-World Examples:

// Basic IF — Salary High or Low: =IF(E2>60000, "High", "Low") 
// Rahul (75000) → "High" 
// Amit (48000) → "Low"

// Bonus Eligibility:
=IF(E2>=70000, "Bonus Eligible", "Not Eligible")

// Numeric Return (10% Bonus):
=IF(E2>80000, E2*0.10, 0)

// Salary > 80000 → 10% bonus, else 0

// Text Comparison:
=IF(C2="IT", "Tech Team", "Non-Tech")

// Dept "IT" → "Tech Team", else "Non-Tech"

// Age Category:
=IF(G2

📊 Results Table:

⚠️ Common Mistakes:

• Mistake: Text values mein quotes bhool jaana → =IF(A1=Hello, "Yes", "No") — Hello column name samjhega!
  Fix: Quotes zaroori: =IF(A1="Hello", "Yes", "No")
• Mistake: Equal sign wrong lagana → =IF(A1==5, ...) double equal galat!
  Fix: Excel mein single = use karo: =IF(A1=5, ...)
• Mistake: Complex conditions mein AND/OR na use karna → 3 nested IFs likhna.
  Fix: Multiple conditions ke liye AND/OR use karo (Topic 4).

💬 Interview Questions:

🔍 Definition: Nested IF is when you put one IF inside another IF's value_if_true or value_if_false argument. Used when you need multiple conditions with different outcomes — like grading systems, salary tiers, or status categories.

🎯 Samjho Simple Bhasha Mein: Ek IF sirf 2 outcomes de sakta hai — TRUE ya FALSE. But real life mein 3, 4, 5 categories hoti hain. Jaise grade — A, B, C, D, F. Toh IF ke andar IF likhte hain — pehle A check, nahi toh B check, nahi toh C, aur so on. Yeh sequential check karta hai upar se neeche.

💡 Nested IF Structure:

=IF(cond1, result1,
IF(cond2, result2,
IF(cond3, result3, default)))

Rule: Excel top se check karta hai — pehli TRUE condition ka result return karta hai. Maximum 64 IFs nest kar sakte hain (but 3-4 se zyada complicated ho jaata hai).

💻 Real-World Examples:

// Salary Grade System: =IF(E2>=90000, "A", IF(E2>=70000, "B", IF(E2>=50000, "C", "D")))

// Results:

// Anjali (95000) → A

// Rahul (75000) → B

// Ravi (53000) → C

// Kavita (45000) → D

// Age Category:
=IF(G2

// City Region:
=IF(D2="Delhi", "North",
IF(D2="Mumbai", "West",
IF(D2="Bangalore", "South",
IF(D2="Chennai", "South", "Other"))))

// Bonus Percentage based on Salary:
=IF(E2>=90000, E20.20,
IF(E2>=70000, E20.15,
IF(E2>=50000, E20.10, E20.05)))

📊 Grade Results:

⚡ Important: Nested IF conditions ORDER mein hone chahiye — highest to lowest ya vice versa. Excel top se check karta hai — pehla TRUE match milte hi ruk jaata hai. 4-5 se zyada nested IFs use karne se acha IFS function use karo (Topic 3).

⚠️ Common Mistakes:

• Mistake: Wrong order mein conditions likhna → IF(E2>=50000, "C", IF(E2>=90000, "A", ...)) — A kabhi return nahi hoga!
  Fix: Highest to lowest order mein likho — top se check hota hai.
• Mistake: Closing brackets bhool jaana → Nested IF mein saare ) balance karna.
  Fix: Har IF ke liye ek ) — count karke check karo.
• Mistake: Bahut nested IFs → Formula unreadable ho jaata hai.
  Fix: 4+ conditions ke liye IFS ya SWITCH use karo.

💬 Interview Questions:

🔍 Definition: IFS is a modern function (Excel 2019+) that replaces nested IFs. It checks multiple conditions in sequence and returns the value corresponding to the first TRUE condition. Cleaner and easier to read than nested IFs.

🎯 Samjho Simple Bhasha Mein: IFS matlab "multiple IF ek hi function mein." Nested IF mein 4 IFs likhne padte the — brackets ka jhamela, readability kharab. IFS mein simple sequence — condition1, result1, condition2, result2. Cleaner, faster, easier to debug. Excel 365/2019+ mein hai — older versions mein nahi.

💡 IFS Syntax:

=IFS(cond1, result1, cond2, result2, cond3, result3, ...)

Default catch-all: Last condition ke liye TRUE use karo:
=IFS(cond1, result1, cond2, result2, TRUE, default_result)

Maximum 127 conditions supported.

💻 Real-World Examples:

// Salary Grade — IFS version: =IFS(E2>=90000, "A", E2>=70000, "B", E2>=50000, "C", TRUE, "D")
// Compare with Nested IF (harder to read):
// =IF(E2>=90000,"A",IF(E2>=70000,"B",IF(E2>=50000,"C","D")))

// Department Category:
=IFS(C2="IT", "Technical",
C2="HR", "People",
C2="Sales", "Revenue",
C2="Finance", "Money",
TRUE, "Other")

// City to Region:
=IFS(D2="Delhi", "North",
D2="Mumbai", "West",
OR(D2="Bangalore", D2="Chennai"), "South",
TRUE, "Other")

// Performance Rating:
=IFS(E2>=95000, "Outstanding",
E2>=80000, "Excellent",
E2>=65000, "Good",
E2>=50000, "Average",
TRUE, "Below Average")

📊 IFS vs Nested IF — Comparison:

⚠️ Common Mistakes:

• Mistake: Default catch-all bhool jaana → Agar koi condition match nahi hui toh #N/A error.
  Fix: Last mein TRUE, "Default" zaroor lagao.
• Mistake: Older Excel version mein IFS use karna → #NAME? error.
  Fix: Excel 2016 aur pehle mein Nested IF use karo. IFS Excel 2019+ mein hai.
• Mistake: Condition order galat → Overlapping conditions.
  Fix: Sequential order — highest to lowest ya specific to general.

💬 Interview Questions:

🔍 Definition: AND returns TRUE if ALL conditions are true. OR returns TRUE if ANY condition is true. NOT reverses TRUE to FALSE and vice versa. These are typically used inside IF to create complex multi-condition logic.

🎯 Samjho Simple Bhasha Mein: AND matlab "dono conditions honi chahiye" — jaise "salary 60K+ AND age 30+." OR matlab "koi ek bhi ho toh chalega" — jaise "IT OR Sales department." NOT matlab "ulta" — jaise "NOT Delhi" matlab Delhi ke alawa. Yeh operators IF ke saath combine karke complex logic banate hain.

💡 Syntax:

=AND(cond1, cond2, cond3, ...) → TRUE if ALL true
=OR(cond1, cond2, cond3, ...) → TRUE if ANY true
=NOT(condition) → Reverses TRUE/FALSE

Usually used inside IF:
=IF(AND(A1>60, B1>30), "Yes", "No")

💻 Real-World Examples:

// AND — Bonus Eligible (2 conditions): =IF(AND(E2>=70000, G2>=30), "Bonus", "No Bonus") 
// Salary 70K+ AND Age 30+ dono chahiye 
// Priya (85K, 32) → Bonus ✅ 
// Rahul (75K, 28) → No Bonus ❌ (age < 30)

// OR — Priority Department:
=IF(OR(C2="IT", C2="Finance"), "Priority", "Normal")

// IT ya Finance mein se koi bhi → Priority

// Rahul (IT) → Priority

// Amit (Sales) → Normal

// NOT — Exclude Delhi:
=IF(NOT(D2="Delhi"), "Outside Delhi", "Delhi")

// Same as: =IF(D2<>"Delhi", "Outside Delhi", "Delhi")

// Complex — AND + OR combined:
=IF(AND(E2>=70000, OR(C2="IT", C2="Finance")),
"Senior Tech/Finance", "Other")

// High salary (70K+) AND (IT ya Finance)

// Multiple AND conditions:
=IF(AND(E2>=60000, G2

// 60K+ salary AND under 35 AND Mumbai based

📊 Truth Table:

⚠️ Common Mistakes:

• Mistake: AND/OR ko IF ke bina use karke direct result expect karna → AND(A>5, B>10) sirf TRUE/FALSE deta hai.
  Fix: IF ke andar wrap karo: =IF(AND(A>5, B>10), "Yes", "No")
• Mistake: Multiple OR conditions ke liye multiple IF likhna.
  Fix: Ek OR mein sab conditions daal do: OR(A=1, A=2, A=3)
• Mistake: AND ka use OR ki jagah → "IT AND HR" — koi employee dono departments mein nahi ho sakta!
  Fix: Logic samjho — "yeh ya woh" hai toh OR use karo.

💬 Interview Questions:

🔍 Definition: IFERROR catches ANY Excel error (#N/A, #VALUE!, #DIV/0!, #REF!, #NAME?) and returns a custom value. IFNA catches ONLY #N/A errors (from VLOOKUP not finding a match). Both make reports look professional by hiding ugly error messages.

🎯 Samjho Simple Bhasha Mein: Excel formulas kabhi fail hote hain — #N/A, #DIV/0! jaise ugly errors dikhte hain reports mein. IFERROR unko catch karke friendly message dikhata hai. Jaise "Not Found" ya "N/A" ya blank. Professional reports mein IFERROR essential hai — client ko #N/A errors dekh ke lagega tumhe Excel nahi aata!

💡 Syntax:

=IFERROR(formula, value_if_error)
=IFNA(formula, value_if_na)

Common Excel Errors:
#N/A → Value not found (VLOOKUP)
#DIV/0! → Division by zero
#VALUE! → Wrong data type
#REF! → Invalid cell reference
#NAME? → Formula name wrong
#NUM! → Invalid number

💻 Real-World Examples:

// IFERROR with VLOOKUP: =IFERROR(VLOOKUP("Vikas", A:G, 2, FALSE), "Not Found") // Vikas nahi mila → "Not Found" // Without IFERROR → #N/A ugly!
// IFERROR with Division:
=IFERROR(E2/G2, "Cannot Divide")
// Age 0 hoga toh #DIV/0! → "Cannot Divide"

// IFERROR — return 0 instead of error:
=IFERROR(A1/B1, 0)
// Error case mein 0 return karta hai

// IFERROR — return blank:
=IFERROR(VLOOKUP(...), "")
// Cell blank dikhega error ki jagah

// IFNA — only catches #N/A:
=IFNA(VLOOKUP("Vikas", A:G, 2, FALSE), "Not in List")
// Sirf #N/A catch karega, other errors nahi

// Nested IFERROR — Multiple fallbacks:
=IFERROR(VLOOKUP(A1, Sheet1!A:B, 2, 0),
IFERROR(VLOOKUP(A1, Sheet2!A:B, 2, 0), "Not Found"))
// Pehle Sheet1 mein dhundho, nahi mila toh Sheet2, phir "Not Found"

📊 IFERROR vs IFNA:

⚠️ Common Mistakes:

• Mistake: IFERROR ka overuse → Real formula bugs hide ho jaate hain!
  Fix: Formula debug karo pehle, phir IFERROR wrap karo. Blindly errors hide mat karo.
• Mistake: IFERROR bina fallback value ke → =IFERROR(A1/B1) — second argument bhool gaye!
  Fix: Hamesha value_if_error do: =IFERROR(A1/B1, 0)
• Mistake: IFERROR ke bajaye IFNA use karna zaroorat na hone par → Formula limited scope.
  Fix: Sirf #N/A expect ho toh IFNA. Any error possible ho toh IFERROR.

💬 Interview Questions:

🔍 Definition: SWITCH evaluates one expression against a list of values and returns the result corresponding to the first match. Cleaner than nested IF when you're checking equality against multiple specific values. Available in Excel 2019+.

🎯 Samjho Simple Bhasha Mein: SWITCH matlab "yeh value equal to kya hai — us hisaab se return karo." Nested IF mein ek hi value ko baar baar likhna padta hai (A1="X", A1="Y"). SWITCH mein ek baar A1 do — phir sirf values list karo. Bahut clean hai! Programming languages ke switch-case jaisa hi.

💡 SWITCH Syntax:

=SWITCH(expression, value1, result1, value2, result2, ..., [default])

expression: Jo value check karni hai
value/result pairs: Match cases
default: Optional — koi match nahi mila toh yeh

Only checks EQUALITY — not > or <. For ranges use IFS.

💻 Real-World Examples:

// Department to Head Name: =SWITCH(C2, "IT", "Rajesh Verma", "HR", "Sunita Patil", "Sales", "Vikram Rao", "Finance", "Ananya Kumar", "Unknown")
// City to Region:
=SWITCH(D2,
"Delhi", "North",
"Mumbai", "West",
"Bangalore", "South",
"Chennai", "South",
"Other")

// Compare with Nested IF (harder):
// =IF(D2="Delhi","North",IF(D2="Mumbai","West",...))

// Grade to Points:
=SWITCH(H2,
"A", 10,
"B", 8,
"C", 6,
"D", 4,
0)

// Month Number to Quarter:
=SWITCH(MONTH(F2),
1, "Q1", 2, "Q1", 3, "Q1",
4, "Q2", 5, "Q2", 6, "Q2",
7, "Q3", 8, "Q3", 9, "Q3",
10, "Q4", 11, "Q4", 12, "Q4")

📊 SWITCH vs Nested IF vs IFS:

⚠️ Common Mistakes:

• Mistake: Range comparisons SWITCH mein karna → SWITCH sirf equality check karta hai, greater/less than nahi!
  Fix: Ranges ke liye IFS use karo.
• Mistake: Default value bhool jaana → No match hone par #N/A error.
  Fix: Last mein default value do: SWITCH(A1, "X", 1, "Y", 2, "Other")
• Mistake: Excel 2016 ya pehle mein SWITCH use karna → #NAME? error.
  Fix: Older Excel mein Nested IF use karo.

💬 Interview Questions:

🔍 Definition: COUNTIF counts cells that meet ONE criteria. COUNTIFS counts cells that meet MULTIPLE criteria (all conditions must be true — AND logic). Essential for data analysis, dashboards, and reports.

🎯 Samjho Simple Bhasha Mein: COUNTIF matlab "condition-wise count." Simple COUNT sab count karta hai — COUNTIF sirf woh count karta hai jo condition satisfy karte hain. Jaise "IT department mein kitne employees hain?" ya "60000+ salary wale kitne hain?" COUNTIFS ek se zyada conditions ke liye — "IT department mein 60000+ salary wale kitne hain?"

💡 Syntax:

=COUNTIF(range, criteria)
=COUNTIFS(range1, cri1, range2, cri2, ...)

Criteria examples:
"IT" → exact text
">60000" → greater than
"<=30" → less than or equal
"*Sharma*" → contains Sharma
"<>IT" → not equal to IT

💻 Real-World Examples:

// COUNTIF — Single Condition: =COUNTIF(C2:C11, "IT") 
// Result: 3 (IT department mein 3 employees)
=COUNTIF(E2:E11, ">60000")

// Result: 6 (60K+ salary wale 6)

=COUNTIF(D2:D11, "Delhi")

// Result: 3 (Delhi mein 3 employees)

// Wildcards:
=COUNTIF(B2:B11, "R*")

// R se shuru: Rahul, Ravi → 2

=COUNTIF(B2:B11, "a")

// Naam mein "a" wale: 8+

// COUNTIFS — Multiple Conditions:
=COUNTIFS(C2:C11, "IT", E2:E11, ">80000")

// IT AND salary > 80000 → 2 (Sneha, Anjali)

=COUNTIFS(D2:D11, "Mumbai", G2:G11, "

// Mumbai AND age 

=COUNTIFS(C2:C11, "Sales", E2:E11, ">=50000")

// Sales AND salary 50K+ → 1 (Suresh)

// Using cell reference in criteria:
=COUNTIF(C2:C11, C2)

// C2 ki value ka count

=COUNTIF(E2:E11, ">"&AVERAGE(E2:E11))

// Average se zyada salary wale

📊 Practice Results:

⚠️ Common Mistakes:

• Mistake: Comparison operators bina quotes ke → COUNTIF(E:E, >60000) galat!
  Fix: COUNTIF(E:E, ">60000") — quotes zaroori.
• Mistake: Cell reference ko criteria mein directly use karna with operator.
  Fix: Concatenate karo: COUNTIF(E:E, ">"&A1)
• Mistake: COUNTIFS mein ranges different sizes ke → Error!
  Fix: All ranges same size hone chahiye — same number of rows.

💬 Interview Questions:

🔍 Definition: SUMIF adds cells that meet ONE criteria. SUMIFS adds cells that meet MULTIPLE criteria. Most powerful functions for financial analysis, sales reports, and category-wise totals.

🎯 Samjho Simple Bhasha Mein: SUMIF matlab "condition-based total." Simple SUM sab jodta hai — SUMIF sirf specific rows ka sum karta hai. Jaise "IT department ki total salary kya hai?" ya "60000+ salary wale kitna kama rahe hain total?" Business reports mein SUMIF/SUMIFS bahut common hain.

💡 Syntax:

=SUMIF(range, criteria, [sum_range])
=SUMIFS(sum_range, cri_range1, cri1, cri_range2, cri2, ...)

SUMIF: range check karta hai, sum_range se jod karta hai
SUMIFS: Order alag — sum_range PEHLE aata hai!

Important: SUMIF aur SUMIFS ka argument order alag hai — dhyaan rakho!

💻 Real-World Examples:

// SUMIF — Total salary by department: =SUMIF(C2:C11, "IT", E2:E11) // IT dept total salary: 75000+92000+95000 = 262000
=SUMIF(C2:C11, "Sales", E2:E11)
// Sales dept total: 48000+45000+51000 = 144000

// SUMIF with comparison:
=SUMIF(E2:E11, ">70000")
// 70K+ salaries ka sum (no sum_range needed here)

=SUMIF(G2:G11, "
// Age

// SUMIFS — Multiple conditions:
=SUMIFS(E2:E11, C2:C11, "IT", D2:D11, "Bangalore")
// IT AND Bangalore ki total salary
// 92000 + 95000 = 187000

=SUMIFS(E2:E11, C2:C11, "Finance", G2:G11, ">=30")
// Finance dept AND age 30+ ki total salary

=SUMIFS(E2:E11, D2:D11, "Delhi", E2:E11, ">50000")
// Delhi AND salary > 50000 ka sum

// Wildcards:
=SUMIF(B2:B11, "Sharma", E2:E11)
// Sharma naam wale sab ki total salary

📊 Department-wise Salary Analysis:

⚡ Important: SUMIF aur SUMIFS ka argument ORDER alag hai! SUMIF mein sum_range LAST mein hai. SUMIFS mein sum_range FIRST mein hai. Yeh Excel ki quirk hai — beginners often confuse hote hain!

⚠️ Common Mistakes:

• Mistake: SUMIFS mein sum_range last mein daalna → SUMIF ki tarah — Error!
  Fix: SUMIFS mein sum_range FIRST hai: =SUMIFS(sum_range, criteria_range1, criteria1, ...)
• Mistake: Criteria range aur sum range different sizes ke → Wrong results.
  Fix: All ranges same size hone chahiye.
• Mistake: Comparison operators bina quotes ke.
  Fix: ">60000", "<=30" — hamesha quotes mein.

💬 Interview Questions:

🔍 Definition: AVERAGEIF calculates the average of cells that meet ONE criteria. AVERAGEIFS averages cells meeting MULTIPLE criteria. Works exactly like SUMIF/SUMIFS but returns average instead of sum.

🎯 Samjho Simple Bhasha Mein: AVERAGEIF matlab "condition-wise average." Jaise "IT department ki average salary kya hai?" ya "30 saal se kam wale employees ki average salary?" HR analytics mein bahut use hota hai — department-wise comparison, age-wise trends. Same syntax jaisa SUMIF ka hai.

💡 Syntax:

=AVERAGEIF(range, criteria, [average_range])
=AVERAGEIFS(avg_range, cri_range1, cri1, cri_range2, cri2, ...)

Same order confusion as SUMIF/SUMIFS — average_range last in AVERAGEIF, first in AVERAGEIFS!

💻 Real-World Examples:

// AVERAGEIF — Department-wise average salary: =AVERAGEIF(C2:C11, "IT", E2:E11) // IT dept average: (75000+92000+95000)/3 = 87333
=AVERAGEIF(C2:C11, "Sales", E2:E11)
// Sales dept average: (48000+45000+51000)/3 = 48000

// AVERAGEIF with comparison:
=AVERAGEIF(G2:G11, ">=30", E2:E11)
// Age 30+ wale ki average salary

// AVERAGEIFS — Multiple conditions:
=AVERAGEIFS(E2:E11, C2:C11, "IT", G2:G11, ">30")
// IT AND age > 30 wale ki average salary
// Sneha (35) + Anjali (38) = 93500 average

=AVERAGEIFS(E2:E11, D2:D11, "Mumbai", E2:E11, ">=60000")
// Mumbai AND salary 60K+ ki average

// City-wise average:
=AVERAGEIF(D2:D11, "Delhi", E2:E11)
// Delhi employees ki average salary

📊 Analytics Dashboard:

⚠️ Common Mistakes:

• Mistake: AVERAGEIFS argument order galat.
  Fix: Same as SUMIFS — average_range FIRST mein.
• Mistake: Text cells count karne ki koshish → AVERAGEIF sirf numbers ka average karta hai.
  Fix: Text ke liye COUNTIF use karo.
• Mistake: Zero cells include hone se average galat.
  Fix: Zero exclude karo: AVERAGEIF(E:E, ">0")

💬 Interview Questions:

🔍 Definition: MAXIFS returns the largest value from cells that meet multiple criteria. MINIFS returns the smallest value. Available in Excel 2019+. Powerful for finding highest/lowest in specific categories.

🎯 Samjho Simple Bhasha Mein: MAXIFS matlab "condition-wise sabse zyada." Jaise "IT department mein sabse zyada salary kya hai?" ya "Delhi mein youngest employee ki age?" Simple MAX poore data mein sabse bada dhundhta hai — MAXIFS specific criteria wale mein. MINIFS bilkul opposite — sabse chhota.

💡 Syntax:

=MAXIFS(max_range, cri_range1, cri1, cri_range2, cri2, ...)
=MINIFS(min_range, cri_range1, cri1, cri_range2, cri2, ...)

Same order as SUMIFS — max_range/min_range FIRST!

Excel 2019+ only. Older versions mein array formulas use karne padte the.

💻 Real-World Examples:

// MAXIFS — Highest salary by department: =MAXIFS(E2:E11, C2:C11, "IT") 
// IT dept ka max salary: 95000 (Anjali)
=MAXIFS(E2:E11, C2:C11, "Sales")

// Sales max: 51000 (Suresh)

// MINIFS — Lowest salary:
=MINIFS(E2:E11, C2:C11, "IT")

// IT min salary

=MINIFS(G2:G11, D2:D11, "Bangalore")

// Bangalore mein youngest age

// Multiple criteria:
=MAXIFS(E2:E11, C2:C11, "IT", D2:D11, "Bangalore")

// IT AND Bangalore ka highest salary

=MINIFS(E2:E11, C2:C11, "Sales", G2:G11, "

// Sales AND age 

// Age-based:
=MAXIFS(G2:G11, C2:C11, "HR")

// HR dept mein oldest employee

=MINIFS(G2:G11, D2:D11, "Delhi")

// Delhi mein youngest

📊 Department-wise Analysis:

⚠️ Common Mistakes:

• Mistake: Excel 2016 ya pehle mein MAXIFS use karna → #NAME? error.
  Fix: Older versions mein array formula use karo: {=MAX(IF(C:C="IT",E:E))}
• Mistake: Argument order galat — max_range last mein daalna.
  Fix: max_range/min_range FIRST hai — SUMIFS jaisa.
• Mistake: No match hone par 0 return hota hai — misleading.
  Fix: Wrap with IFERROR: =IFERROR(MAXIFS(...), "No Match")

💬 Interview Questions:

🎯 Quick Reference — Which Function to Use?

2 outcomes only? → IF

3+ outcomes with ranges? → IFS or Nested IF

Match specific values? → SWITCH

Multiple conditions together? → AND / OR inside IF

Handle errors? → IFERROR (any) or IFNA (only #N/A)

Count with condition? → COUNTIF / COUNTIFS

Sum/Average/Max with condition? → SUMIFS / AVERAGEIFS / MAXIFS

Next: Data Insights Excel Masterclass — Part 4

Part 4 mein hum cover karenge: Text Functions — LEFT, RIGHT, MID, LEN, FIND, SEARCH, CONCATENATE, TEXTJOIN, UPPER, LOWER, PROPER, TRIM, CLEAN, SUBSTITUTE, TEXT, VALUE — Excel ka text manipulation Data Insights par.

Happy Learning & Keep Excelling! 🚀