📊 Excel Formulas — Part 3

Conditional Calculations

This part focuses on conditional calculations — extremely useful when working with real-world datasets.

1️⃣ SUMIF

Adds values that meet one condition. 
=SUMIF(A2:A10,"Sales",B2:B10)

Example: Add sales only for rows where the category is Sales.

2️⃣ SUMIFS

Adds values based on multiple conditions. 
=SUMIFS(C2:C10,A2:A10,"East",B2:B10,"Laptop")

Example: Calculate laptop sales only for the East region.

Syntax: 
=SUMIFS(sum_range, criteriaᵣange1, criteria1, criteriaᵣange2, criteria2)

3️⃣ COUNTIF

Counts cells that meet one condition. 
=COUNTIF(B2:B10,">=50")

Example: Count how many students scored 50 or more.

4️⃣ COUNTIFS

Counts cells/rows that meet multiple conditions. 
=COUNTIFS(A2:A10,"East",B2:B10,">=100")

Example: Count transactions from the East region where sales are at least 100.

5️⃣ AVERAGEIF

Calculates an average based on one condition. 
=AVERAGEIF(A2:A10,"Sales",B2:B10)

Example: Calculate the average sales for the Sales category.

6️⃣ AVERAGEIFS

Calculates an average based on multiple conditions. 
=AVERAGEIFS(C2:C10,A2:A10,"East",B2:B10,"Laptop")

Example: Calculate the average laptop sales in the East region.

💡 Sample Data

Region | Product | Sales 
East | Laptop | 500 
West | Mouse | 200 
East | Laptop | 700 
South | Keyboard | 300 
East | Mouse | 250 

Try calculating:

1. Total East sales → SUMIF
2. Total East Laptop sales → SUMIFS
3. Number of transactions above 300 → COUNTIF
4. Number of East transactions → COUNTIF
5. Number of East Laptop transactions → COUNTIFS
6. Average East sales → AVERAGEIF
7. Average East Laptop sales → AVERAGEIFS

🧠 Remember

• IF → Make a decision
• SUMIF → Add based on a condition
• SUMIFS → Add based on multiple conditions
• COUNTIF → Count based on a condition
• COUNTIFS → Count based on multiple conditions
• AVERAGEIF → Average based on a condition
• AVERAGEIFS → Average based on multiple conditions

Double Tap ❤️ For Part-4