DAY 9: Excel - Advanced Functions (SUMIFS, COUNTIFS, Text Functions)

📝 Setup Instructions:

1. Open your Superstore Sales dataset
2. Ensure columns: Region, Category, Sales, Profit, Product Name, Customer Name, etc.
3. Today builds on Day 3 (SUMIF/COUNTIF) - now with MULTIPLE criteria!

💡 Advanced Functions: Day 3 taught SUMIF (one condition). Today you'll master SUMIFS, COUNTIFS, AVERAGEIFS (multiple conditions) plus text manipulation!

Exercise 1: SUMIFS - Sum with Multiple Criteria

Step-by-Step Instructions:

1. Calculate total sales for West region AND Technology category
2. In an empty cell, type:

   =SUMIFS(D:D, C:C, "West", E:E, "Technology")

   Breaking it down:
   • D:D = Sum range (Sales column)
   • C:C, "West" = First criteria: Region = West
   • E:E, "Technology" = Second criteria: Category = Technology
3. Press Enter
4. Try more combinations:

   =SUMIFS(D:D, C:C, "East", F:F, ">500")

   Sum sales for East region where Sales > 500

💡 SUMIFS vs SUMIF:

• SUMIF: One condition - =SUMIF(range, criteria, sum_range)
• SUMIFS: Multiple conditions - =SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2...)
• Note: SUMIFS puts sum_range FIRST!

✅ Expected Result: Total sales matching ALL specified criteria

Exercise 2: COUNTIFS & AVERAGEIFS - More Multi-Criteria Functions

Step-by-Step Instructions:

1. COUNTIFS - Count rows meeting multiple conditions:

   =COUNTIFS(C:C, "West", D:D, ">1000")

   Counts West region orders with Sales > 1000
2. AVERAGEIFS - Average with multiple criteria:

   =AVERAGEIFS(I:I, C:C, "East", D:D, ">0")

   Average profit for East region where Sales > 0
3. Three criteria example:

   =SUMIFS(D:D, C:C, "West", E:E, "Furniture", I:I, ">0")

   Sum West + Furniture + Profitable orders

💡 Pattern Recognition:

• All end with "IFS" for multiple criteria
• Format: =XXXIFS(value_range, criteria_range1, criteria1, criteria_range2, criteria2...)
• Can have many criteria pairs (not limited to 2!)

✅ Expected Result: Counts and averages based on multiple conditions

Exercise 3: Text Functions - Extract and Manipulate

Step-by-Step Instructions:

1. LEFT, RIGHT, MID - Extract parts of text:

   =LEFT(A2, 3)  // First 3 characters
   =RIGHT(A2, 4)  // Last 4 characters
   =MID(A2, 2, 5)  // 5 characters starting at position 2

2. CONCATENATE or & - Combine text:

   =B2 & "-" & C2 & "-" & D2  // Join with hyphens
   =CONCATENATE(B2, " ", C2)  // Join with space

3. UPPER, LOWER, PROPER - Change case:

   =UPPER(A2)  // ALL CAPS
   =LOWER(A2)  // all lowercase
   =PROPER(A2)  // Title Case

4. TRIM - Remove extra spaces:

   =TRIM(A2)  // Removes leading/trailing/extra spaces

5. LEN - Count characters:

   =LEN(A2)  // Number of characters

💡 Practical Example - Create Product Code:

=UPPER(LEFT(B2,3)) & "-" & C2

This takes first 3 letters of product name (uppercase) + hyphen + category

✅ Expected Result: Extracted, combined, or formatted text as needed

Exercise 4: IFERROR - Handle Errors Gracefully

Step-by-Step Instructions:

1. Basic IFERROR with division:

   =IFERROR(I2/D2, 0)

   Calculates Profit/Sales; if error (division by zero), returns 0
2. IFERROR with VLOOKUP (from Day 5):

   =IFERROR(VLOOKUP(A2, Products!A:C, 2, FALSE), "Not Found")

   If product not found, shows "Not Found" instead of #N/A
3. Nested error handling:

   =IFERROR(I2/D2, IFERROR(I2/E2, "No Data"))

   Try first calculation; if error, try second; if still error, show "No Data"

💡 Common Errors Handled:

• #DIV/0! - Division by zero
• #N/A - VLOOKUP/XLOOKUP not found
• #VALUE! - Wrong data type
• #REF! - Invalid cell reference

✅ Expected Result: Clean output with user-friendly messages instead of error codes

Exercise 5: Combining Advanced Functions - Real-World Example

Step-by-Step Instructions:

1. Create a dashboard cell showing: "West region Technology sales for profitable orders"
2. Use this formula:

   =IFERROR(
     "West Tech Profitable Sales: $" &
     TEXT(SUMIFS(D:D, C:C, "West", E:E, "Technology", I:I, ">0"), "#,##0"),
     "Data Not Available"
   )

   Breaking it down:
   • SUMIFS calculates the sum (multiple criteria)
   • TEXT formats the number with commas
   • & concatenates the label and value
   • IFERROR handles any errors
3. Result looks like: "West Tech Profitable Sales: $125,450"

🎯 Function Nesting:

• You can nest functions inside each other
• Excel evaluates from inside out
• Combine multiple functions for powerful calculations
• Always test each part separately first!

✅ Expected Result: Professional-looking formatted output combining multiple advanced functions

🎯 Practice Goal Checkpoint:

By completing these 5 exercises, you should now be able to:

• Use SUMIFS, COUNTIFS, AVERAGEIFS for multiple criteria
• Manipulate text with LEFT, RIGHT, MID, CONCATENATE, UPPER/LOWER/PROPER, TRIM
• Handle errors gracefully with IFERROR
• Combine multiple functions in complex formulas
• Create professional, formatted outputs

Key Insight: Advanced functions eliminate manual work! Instead of filtering data repeatedly, one formula can calculate exactly what you need across any criteria.

Comparison with Previous Days:

• Day 3: SUMIF (one condition)
• Day 9: SUMIFS (multiple conditions) + text manipulation + error handling
• Building complexity step by step!

Common Use Cases:

• Complex reports with multiple filters
• Data cleaning and standardization
• Dashboard calculations
• Automated data processing

Take a 5-minute break, then move on to Independent Practice!