SUMIF and SUMIFS in Excel - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the formula to sum values in column B where column A equals "Apple".

Excel

=SUMIF(A:A, [1], B:B)

Drag options to blanks, or click blank then click option'

A"Apple"

BApple

CA1

D"A"

Attempts:

3 left

💡 Hint

Common Mistakes

Forgetting to put quotes around text criteria.

Using a cell reference instead of the text string.

The SUMIF function needs the criteria as a text string, so it must be enclosed in quotes.

2fill in blank

medium

Complete the formula to sum values in column C where column B is greater than 100.

Excel

=SUMIF(B:B, [1], C:C)

Drag options to blanks, or click blank then click option'

A">100"

B100

C>=100

D"100"

Attempts:

3 left

💡 Hint

Common Mistakes

Using just 100 without an operator.

Putting the number 100 without quotes.

The criteria must be a string with the comparison operator and number, like ">100".

3fill in blank

hard

Fix the error in the formula to sum values in column D where column A equals "Banana" and column B is less than 50.

Excel

=SUMIFS(D:D, A:A, [1], B:B, [2])

Drag options to blanks, or click blank then click option'

A"Banana"

BBanana

C"<50"

D<50

Attempts:

3 left

💡 Hint

Common Mistakes

Not using quotes around text or comparison criteria.

Using raw numbers or text without quotes.

Text criteria like "Banana" must be in quotes. Also, comparison criteria like "<50" must be in quotes.

4fill in blank

hard

Fill both blanks to sum values in column E where column C equals "Yes" and column D is not equal to 0.

Excel

=SUMIFS(E:E, C:C, [1], D:D, [2])

Drag options to blanks, or click blank then click option'

A"Yes"

B"No"

C"<>0"

D"=0"

Attempts:

3 left

💡 Hint

Common Mistakes

Using "No" instead of "Yes".

Using "=0" instead of "<>0".

Use "Yes" to match text in column C and "<>0" to exclude zeros in column D.

5fill in blank

hard

Fill all three blanks to sum values in column F where column A equals "Orange", column B is greater than 20, and column C equals "Complete".

Excel

=SUMIFS(F:F, A:A, [1], B:B, [2], C:C, [3])

Drag options to blanks, or click blank then click option'

A"Orange"

B">20"

C"Complete"

D"Incomplete"

Attempts:

3 left

💡 Hint

Common Mistakes

Forgetting quotes around text or comparison criteria.

Using wrong comparison operators.

Use "Orange" and "Complete" as text criteria, and ">20" for the numeric comparison.

Practice

1. What does the SUMIF function do in Excel?

easy

A. Adds numbers in a range that meet one condition

B. Adds numbers in multiple ranges without conditions

C. Counts how many cells meet a condition

D. Finds the average of numbers in a range

Solution

1. Step 1: Understand the purpose of SUMIF

   SUMIF adds values only if they meet a single specified condition.

2. Step 2: Compare with other options

   Counting cells or averaging are different functions; SUMIF sums conditionally.

3. Final Answer:

   Adds numbers in a range that meet one condition -> Option A

4. Quick Check:

   SUMIF = sum with one condition [OK]

Hint: SUMIF sums with one condition, SUMIFS with many [OK]

Common Mistakes:

• Confusing SUMIF with COUNTIF
• Thinking SUMIF works with multiple conditions
• Using SUMIF to average values

2. Which of these is the correct syntax for the SUMIFS function?

easy

A. SUMIFS(criteria_range1, criteria1, sum_range)

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

C. SUMIFS(sum_range, criteria1, criteria_range1)

D. SUMIFS(sum_range, criteria_range1, criteria1)

Solution

1. Step 1: Recall SUMIFS syntax

   SUMIFS requires sum_range first, then pairs of criteria_range and criteria.

2. Step 2: Check each option

   Only SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2) follows the correct order: sum_range, criteria_range1, criteria1, etc.

3. Final Answer:

   SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2) -> Option B

4. Quick Check:

   SUMIFS syntax = sum_range + criteria pairs [OK]

Hint: SUMIFS starts with sum_range, then criteria pairs [OK]

Common Mistakes:

• Swapping sum_range and criteria_range
• Missing criteria pairs
• Using SUMIF syntax for SUMIFS

3. Given this data:

A1:A5 = {10, 20, 30, 40, 50}
B1:B5 = {"Yes", "No", "Yes", "No", "Yes"}

What is the result of =SUMIF(B1:B5, "Yes", A1:A5)?

medium

A. 150

B. 100

C. 120

D. 90

Solution

1. Step 1: Identify matching cells in B1:B5

   Cells with "Yes" are B1, B3, B5 corresponding to A1=10, A3=30, A5=50.

2. Step 2: Sum corresponding values in A1:A5

   Sum = 10 + 30 + 50 = 90.

3. Final Answer:

   90 -> Option D

4. Quick Check:

   Sum of A where B = "Yes" is 90 [OK]

Hint: Sum values where criteria matches in criteria_range [OK]

Common Mistakes:

• Summing all values ignoring criteria
• Confusing Yes and No cells
• Using SUM instead of SUMIF

4. You wrote this formula: =SUMIFS(A1:A5, B1:B5, "Yes", C1:C5, ">10") but it returns #VALUE!. What is the likely problem?

medium

A. Sum range and criteria ranges are different sizes

B. SUMIFS cannot handle multiple criteria

C. Criteria ">10" should be in quotes

D. You must use SUMIF, not SUMIFS

Solution

1. Step 1: Check criteria syntax

   Criteria ">10" is correctly in quotes, so no error there.

2. Step 2: Verify range sizes

   SUMIFS requires sum_range and all criteria_ranges to be the same size; mismatch causes #VALUE! error.

3. Final Answer:

   Sum range and criteria ranges are different sizes -> Option A

4. Quick Check:

   Range size mismatch causes SUMIFS to return #VALUE! [OK]

Hint: Ensure all ranges in SUMIFS have same size [OK]

Common Mistakes:

• Forgetting to quote criteria with operators
• Using SUMIF syntax for multiple criteria
• Assuming SUMIFS sums without matching sizes

5. You have sales data:

Product: A2:A7 = {"Pen", "Pen", "Book", "Pen", "Book", "Book"}
Region: B2:B7 = {"East", "West", "East", "East", "West", "East"}
Sales: C2:C7 = {100, 200, 150, 120, 180, 130}

Which formula sums sales of "Pen" in the "East" region?

hard

A. =SUMIFS(A2:A7, C2:C7, "Pen", B2:B7, "East")

B. =SUMIF(A2:A7, "Pen", C2:C7) + SUMIF(B2:B7, "East", C2:C7)

C. =SUMIFS(C2:C7, A2:A7, "Pen", B2:B7, "East")

D. =SUMIF(C2:C7, "Pen", B2:B7)

Solution

1. Step 1: Identify correct function for multiple conditions

   SUMIFS sums with multiple criteria; SUMIF only handles one.

2. Step 2: Check formula arguments

   =SUMIFS(C2:C7, A2:A7, "Pen", B2:B7, "East") correctly uses sum_range C2:C7, criteria ranges A2:A7 and B2:B7 with criteria "Pen" and "East".

3. Final Answer:

   =SUMIFS(C2:C7, A2:A7, "Pen", B2:B7, "East") -> Option C

4. Quick Check:

   SUMIFS with product and region criteria sums sales [OK]

Hint: Use SUMIFS for multiple conditions, not SUMIF [OK]

Common Mistakes:

• Adding two SUMIF results instead of using SUMIFS
• Mixing sum_range and criteria_range order
• Using SUMIF with multiple criteria