Challenge - 5 Problems
SUMIF and SUMIFS Master
Get all challenges correct to earn this badge!
Test your skills under time pressure!
📊 Formula Result
intermediate2:00remaining
SUMIF with a single condition
Given a list of sales amounts in column B and their corresponding product categories in column A, what is the result of the formula
Data:
A2:A6 = {"Fruit", "Vegetable", "Fruit", "Dairy", "Fruit"}
B2:B6 = {10, 20, 15, 5, 25}
What is the sum of sales for the category "Fruit"?
=SUMIF(A2:A6, "Fruit", B2:B6) if the data is:Data:
A2:A6 = {"Fruit", "Vegetable", "Fruit", "Dairy", "Fruit"}
B2:B6 = {10, 20, 15, 5, 25}
What is the sum of sales for the category "Fruit"?
Attempts:
2 left
💡 Hint
Add only the amounts where the category is exactly "Fruit".
✗ Incorrect
The formula sums values in B2:B6 where the corresponding A2:A6 cells equal "Fruit". The matching rows are 10 (row 2), 15 (row 4), and 25 (row 6). Their sum is 10 + 15 + 25 = 50.
📊 Formula Result
intermediate2:00remaining
SUMIFS with multiple conditions
You have a table with sales data:
Column A: Product Type
Column B: Region
Column C: Sales Amount
Data:
A2:A6 = {"Fruit", "Fruit", "Vegetable", "Fruit", "Vegetable"}
B2:B6 = {"North", "South", "North", "North", "South"}
C2:C6 = {100, 150, 200, 50, 300}
What is the result of the formula
Column A: Product Type
Column B: Region
Column C: Sales Amount
Data:
A2:A6 = {"Fruit", "Fruit", "Vegetable", "Fruit", "Vegetable"}
B2:B6 = {"North", "South", "North", "North", "South"}
C2:C6 = {100, 150, 200, 50, 300}
What is the result of the formula
=SUMIFS(C2:C6, A2:A6, "Fruit", B2:B6, "North")?Attempts:
2 left
💡 Hint
Sum sales where product is "Fruit" and region is "North".
✗ Incorrect
Rows matching both conditions are row 2 (Fruit, North, 100) and row 4 (Fruit, North, 50). Sum is 100 + 50 = 150.
❓ Function Choice
advanced2:00remaining
Choosing the correct formula for conditional sum
You want to sum values in column C where column A equals "Fruit" and column B is not "South". Which formula correctly calculates this?
Attempts:
2 left
💡 Hint
Use SUMIFS with a condition that excludes "South".
✗ Incorrect
Option B uses SUMIFS with two conditions: A equals "Fruit" and B not equal to "South" ("<>South" means not South). This correctly sums only rows where both conditions are met. Option B subtracts sums incorrectly and can double count. Option B sums only where B is "South" which is opposite. Option B sums all rows where B is not "South" ignoring A condition.
📊 Formula Result
advanced2:00remaining
SUMIF with wildcard characters
Given the data in column A:
A2:A6 = {"Apple", "Pineapple", "Banana", "Grape", "Apple Pie"}
and sales in column B:
B2:B6 = {10, 20, 15, 5, 25}
What is the result of
A2:A6 = {"Apple", "Pineapple", "Banana", "Grape", "Apple Pie"}
and sales in column B:
B2:B6 = {10, 20, 15, 5, 25}
What is the result of
=SUMIF(A2:A6, "*Apple*", B2:B6)?Attempts:
2 left
💡 Hint
The asterisks (*) match any text before or after "Apple".
✗ Incorrect
The condition "*Apple*" matches any cell containing "Apple" anywhere. Matches are "Apple" (10), "Pineapple" (20), and "Apple Pie" (25). Sum is 10 + 20 + 25 = 55.
🎯 Scenario
expert3:00remaining
Complex conditional sum with date and text criteria
You have a sales table:
Column A: Product
Column B: Sale Date (dates)
Column C: Region
Column D: Sales Amount
Data example:
A2:A6 = {"Fruit", "Vegetable", "Fruit", "Fruit", "Vegetable"}
B2:B6 = {"2024-01-01", "2024-01-05", "2024-02-01", "2024-01-15", "2024-02-10"}
C2:C6 = {"North", "South", "North", "South", "North"}
D2:D6 = {100, 200, 150, 50, 300}
Which formula sums sales for "Fruit" products sold in "North" region during January 2024?
Column A: Product
Column B: Sale Date (dates)
Column C: Region
Column D: Sales Amount
Data example:
A2:A6 = {"Fruit", "Vegetable", "Fruit", "Fruit", "Vegetable"}
B2:B6 = {"2024-01-01", "2024-01-05", "2024-02-01", "2024-01-15", "2024-02-10"}
C2:C6 = {"North", "South", "North", "South", "North"}
D2:D6 = {100, 200, 150, 50, 300}
Which formula sums sales for "Fruit" products sold in "North" region during January 2024?
Attempts:
2 left
💡 Hint
Use ISO date format (YYYY-MM-DD) in criteria strings for dates.
✗ Incorrect
Option A uses the correct ISO date format "YYYY-MM-DD" which Excel recognizes in criteria strings. Options A, B, and D use other date formats that may not be reliably recognized as dates in SUMIFS criteria, causing incorrect or zero sums.