🚀 Excel Formulas Fundamentals — Part 10

📊 Conditional Functions (SUMIF, SUMIFS, COUNTIF, COUNTIFS, AVERAGEIF, AVERAGEIFS, SUMPRODUCT)

Conditional functions allow you to calculate, count, or average data based on one or more conditions. They are among the most commonly used functions by Data Analysts, Financial Analysts, and Business Analysts.

📌 These functions are frequently asked in Excel interviews and used in business reporting.

🧠 1. SUMIF() – Sum Based on One Condition

SUMIF() adds values that meet a single condition.

Syntax:

=SUMIF(range, criteria, sum_range)

Example:

Data: East 50000, West 30000, East 40000

Formula: =SUMIF(A2:A4,"East",B2:B4)

Result: 90000

📌 Use Cases:

Total sales by region, Total expenses by category, Revenue by product

🎯 2. SUMIFS() – Sum Based on Multiple Conditions

SUMIFS() adds values only when all conditions are met.

Syntax:

=SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2)

Example:

Data: East Laptop 50000, East Mobile 30000, West Laptop 45000

Formula: =SUMIFS(C2:C4,A2:A4,"East",B2:B4,"Laptop")

Result: 50000

📌 Commonly used in dashboards and business reports.

🔢 3. COUNTIF() – Count Based on One Condition

Counts the number of cells that meet a condition.

Syntax:

=COUNTIF(range, criteria)

Example:

Status: Completed, Pending, Completed

Formula: =COUNTIF(A2:A4,"Completed")

Result: 2

📌 Use Cases:

Count completed tasks, Count active customers, Count employees in a department

📋 4. COUNTIFS() – Count Based on Multiple Conditions

Counts records that satisfy multiple conditions.

Syntax:

=COUNTIFS(criteria_range1, criteria1, criteria_range2, criteria2)

Example:

Data: East Laptop, East Mobile, West Laptop

Formula: =COUNTIFS(A2:A4,"East",B2:B4,"Laptop")

Result: 1

📈 5. AVERAGEIF() – Average Based on One Condition

Calculates the average for values matching one condition.

Syntax:

=AVERAGEIF(range, criteria, average_range)

Example:

Data: East 50000, West 30000, East 40000

Formula: =AVERAGEIF(A2:A4,"East",B2:B4)

Result: 45000

📊 6. AVERAGEIFS() – Average Based on Multiple Conditions

Calculates the average when multiple conditions are satisfied.

Syntax:

=AVERAGEIFS(average_range, criteria_range1, criteria1, ...)

Example:

=AVERAGEIFS(C2:C5,A2:A5,"East",B2:B5,"Laptop")

📌 Useful for finding the average sales of a specific product in a specific region.

⚡ 7. SUMPRODUCT() – Multiply and Sum Arrays

SUMPRODUCT() multiplies corresponding values in arrays and returns the sum.

Syntax:

=SUMPRODUCT(array1, array2)

Example:

Data: Quantity 2 Price 500, Quantity 3 Price 700, Quantity 1 Price 1000

Formula: =SUMPRODUCT(A2:A4,B2:B4)

Calculation: (2 × 500) + (3 × 700) + (1 × 1000) = 4100

Result: 4100

📌 Useful for weighted calculations and financial analysis.

🏢 8. Real-World Scenario – Sales Dashboard

Data: East Laptop 50000, East Mobile 30000, West Laptop 45000, West Mobile 25000

Total Sales in East

=SUMIF(A2:A5,"East",C2:C5)

Laptop Sales in West

=SUMIFS(C2:C5,A2:A5,"West",B2:B5,"Laptop")

Number of Mobile Orders

=COUNTIF(B2:B5,"Mobile")