✨ Day 5: Advanced Excel Functions

Conditional & Statistical Mastery

Ghaziabad, Mohan Nagar

Drafted by: HR Prateek Sharma

🎯 Mastering Conditional Functions

Advanced formulas for precise data analysis and filtering

📊 MINIFS Function

MIN

MINIFS - Minimum with Multiple Conditions

Syntax:
=MINIFS(min_range, criteria_range1, criteria1, criteria_range2, criteria2, ...)

💼 Example Use Case:
Find minimum salary for a specific job role (e.g., Intern)

=MINIFS(salary_range, job_role_range, "Intern")

• Purpose: Find the smallest value that meets multiple criteria
• Use in HR: Identify lowest salary by department, role, or experience level
• Advantage: Combine multiple conditions in a single formula

➕ SUMIFS Function

Σ

SUMIFS - Sum with Multiple Conditions

Syntax:
=SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2, ...)

💼 Example 1: Total monthly income of female employees in IT
=SUMIFS(monthly_income, department, "IT", gender, "Female")

💼 Example 2: Total income of female employees in Finance
=SUMIFS(income, dept, "Finance", gender, "Female")

📌 Key Difference:
SUMIF → Single Criteria | SUMIFS → Multiple Criteria

🔢 Comparison: SUMIF vs SUMIFS

SUMIF

Single Condition

Use when you need to sum based on ONE criterion only

=SUMIF(range, criteria, sum_range)

SUMIFS

Multiple Conditions

Use when you need to sum based on TWO or MORE criteria

=SUMIFS(sum_range, range1, criteria1, ...)

🎯 COUNTIFS Function

#

COUNTIFS - Count with Multiple Conditions

Syntax:
=COUNTIFS(criteria_range1, criteria1, criteria_range2, criteria2, ...)

💼 Example: Count female employees with specific marital status
=COUNTIFS(gender_col, "Female", marital_status, "Married")

• Purpose: Count cells that meet multiple criteria simultaneously
• HR Applications: Demographics analysis, workforce segmentation
• Example Uses: Count employees by department + gender, or by age group + designation

🏆 LARGE Function

TOP

LARGE - Find Nth Largest Value

Syntax:
=LARGE(array, n)

Purpose: Returns the nth largest value from a dataset
n = 1: Largest value (1st highest)
n = 2: Second largest value
n = 3: Third largest value, and so on...

💼 Example 1: Find 2nd highest salary
=LARGE(salary_col, 2)

💼 Example 2: Find highest monthly income from table
=LARGE(table1[monthly income], 1)

💡 Pro Tip: LARGE is perfect for finding top performers, highest salaries,
or identifying outliers in your data without sorting!

• Opposite Function: SMALL (finds nth smallest value)
• HR Use Cases: Top salary ranges, performance rankings, bonus calculations
• Advantage: No need to sort data - directly extract ranked values

📋 Function Summary

MINIFS

Find minimum value with conditions

Statistical

SUMIFS

Sum values with multiple criteria

Aggregation

COUNTIFS

Count cells meeting conditions

Counting

LARGE

Find nth largest value

Ranking

Ghaziabad, Mohan Nagar

Design & Content by HR Prateek Sharma

Day 5 - Advanced Excel Functions Training

Conditional Functions • Statistical Analysis • Data Ranking