Data Analytics FULL Course 2026 with GenAI | Part 04

In Data Analytics FULL Course 2026 – Part 04, we move deeper into Advanced Excel by learning three potent formulas used daily by data analysts:

• SUMIFS
• COUNTIFS
• AVERAGEIFS

These formulas are used when multiple conditions are involved. If you want to analyze real business data accurately, mastering these functions is absolutely essential.

Why IFs Functions Are Important in Data Analytics

Earlier, you learned:

• SUM, COUNT, AVERAGE → No condition
• SUMIF, COUNTIF, AVERAGEIF → Single condition

But real-world business questions are never that simple.

Examples:

• Sales in the East region AND January
• Average marks for Class 10 AND Math
• Count orders where category is Electronics AND sales > 40,000

To solve such problems, we use IFs functions.

What are IF’s functions in Excel?

IFs functions allow you to apply multiple conditions while performing aggregation.

These functions are considered Advanced Excel and are heavily used in:

• Dashboards
• MIS reports
• Business analysis
• SQL-like logic in Excel

SUMIFS Formula Explained (With Example)

Syntax:

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

Example:

Find total sales where Region = East AND Month = January

• Sum range → Sales column
• Criteria 1 → Region = East
• Criteria 2 → Month = Jan

Result:

=SUMIFS(C2:C5, A2:A5, "East", B2:B5, "Jan")

✅ Output: 15,000

This formula adds only those sales where both conditions are satisfied.

Practical SUMIFS Use Case (Sales Analysis)

Question:

Calculate total sales where:

• Region = East
• Category = Electronics

Formula:

=SUMIFS(Sales, Region, "East", Category, "Electronics")

📌 Important Tip:
Always lock ranges (using $ or F4) to avoid incorrect calculations when copying formulas.

AVERAGEIFS Formula Explained

Syntax:

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

Example:

Find average marks where:

• Class = 10
• Subject = Math

=AVERAGEIFS(Marks, Class, 10, Subject, "Math")

✅ Output: 85

This calculates the average only for students who satisfy both conditions.

Practical AVERAGEIFS Example (Business Data)

Question:

Find average sales where:

• Category = Furniture
• Region = West

Formula:

=AVERAGEIFS(Sales, Category, "Furniture", Region, "West")

✅ Output: 12,000

AVERAGEIFS is very useful in:

• Performance benchmarking
• Region-wise analysis
• Product pricing evaluation

COUNTIFS Formula Explained

Syntax:

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

⚠️ Important Difference:
COUNTIFS does not require a sum or average range.

COUNTIFS Example (Student Data)

Question:

Count students where:

• Gender = Male
• Score ≥ 60

Formula:

=COUNTIFS(Gender, "Male", Score, ">=60")

✅ Output: 2

Practical COUNTIFS Example (Sales Records)

Question:

Count number of records where:

• Category = Electronics
• Sales ≥ 40,000

Formula:

=COUNTIFS(Category, "Electronics", Sales, ">=40000")

✅ Output: 3

📌 Note:
When using operators like >=, always put them inside double quotes.

Another COUNTIFS Example

Question:

Count records where:

• Region = North
• Units Sold ≥ 4

Formula:

=COUNTIFS(Region, "North", Units, ">=4")

✅ Output: 2

Quick Summary of IFs Functions

Key Learning Points:

• Order of conditions does not matter
• Always lock ranges
• Use double quotes for logical conditions
• IFs functions replicate SQL WHERE logic inside Excel

What’s Coming Next?

In upcoming lectures, you’ll learn:

• Pivot Tables
• Advanced conditions (AND, OR, NOT)
• Excel logic building
• SQL-style data analysis
• Real-world analytics projects

Download Dataset