SUMIF and SUMIFS in Google Sheets - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

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

Google Sheets

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

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

A"A:A"

BApples

CA1

D"Apples"

Attempts:

3 left

💡 Hint

Common Mistakes

Forgetting to put text criteria in quotes

Using a cell reference instead of the text

Using the whole column name as a string

The criteria must be a text string in quotes, so use "Apples".

2fill in blank

medium

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

Google Sheets

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

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

A">100"

B100

C>100

DB1>100

Attempts:

3 left

💡 Hint

Common Mistakes

Using >100 without quotes

Using just 100 without operator

Using a cell reference with operator incorrectly

Criteria with operators must be inside quotes, so use ">100".

3fill in blank

hard

Fix the error in this SUMIFS formula to sum values in D where A is "Bananas" and B is less than 50.

Google Sheets

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

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

A<50

BBananas

C"<50"

D"Bananas"

Attempts:

3 left

💡 Hint

Common Mistakes

Not putting text criteria in quotes

Using a cell reference instead of text

Mixing up criteria order

Not putting operator criteria in quotes

The first criteria must be text in quotes: "Bananas" and the second criteria must be "<50" in quotes.

4fill in blank

hard

Fill both blanks to sum values in D where A is "Bananas" and B is less than 50.

Google Sheets

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

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

A"Bananas"

BBananas

C"<50"

D<50

Attempts:

3 left

💡 Hint

Common Mistakes

Leaving out quotes around criteria

Using operator without quotes

Swapping criteria order

Both criteria must be strings in quotes: "Bananas" and "<50".

5fill in blank

hard

Fill all three blanks to sum values in E where A is "Oranges", B is greater than 20, and C equals "Yes".

Google Sheets

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

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

A"Oranges"

B">20"

C"Yes"

DYes

Attempts:

3 left

💡 Hint

Common Mistakes

Not quoting text criteria

Not quoting operator criteria

Using raw Yes without quotes

All criteria must be strings in quotes: "Oranges", ">20", and "Yes".

Practice

1. What does the SUMIF function do in Google Sheets?

easy

A. Adds numbers that meet one specific condition

B. Adds all numbers in a range regardless of conditions

C. Counts how many cells meet a condition

D. Finds the average of numbers that meet multiple conditions

Solution

1. Step 1: Understand the purpose of SUMIF

   The SUMIF function adds values only if they meet one condition you specify.

2. Step 2: Compare with other options

   Options A, B, and C describe different functions like AVERAGEIFS, SUM, and COUNTIF, not SUMIF.

3. Final Answer:

   Adds numbers that meet one specific 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
• Mixing SUMIF with AVERAGE functions

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

easy

A. SUMIFS(sum_range, criteria1, criteria_range1)

B. SUMIFS(criteria_range1, criteria1, sum_range)

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

D. SUMIFS(sum_range, criteria_range1, criteria1)

Solution

1. Step 1: Recall SUMIFS syntax

   The correct syntax is SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...).

2. Step 2: Check each option

   SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2) matches the correct order: sum_range first, then pairs of criteria ranges and criteria. Others have wrong order or missing parts.

3. Final Answer:

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

4. Quick Check:

   SUMIFS = sum_range first, then criteria pairs [OK]

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

Common Mistakes:

• Putting criteria before sum_range
• Missing criteria_range or criteria pairs
• Using SUMIF syntax for SUMIFS

3. Given this data in columns A and B:

A1:A5 = {"Apple", "Banana", "Apple", "Orange", "Banana"}
B1:B5 = {10, 20, 30, 40, 50}

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

medium

A. 20

B. 70

C. 50

D. 30

Solution

1. Step 1: Identify matching rows for "Banana"

   In A1:A5, "Banana" appears in A2 and A5.

2. Step 2: Sum corresponding values in B1:B5

   Values at B2 and B5 are 20 and 50. Sum = 20 + 50 = 70.

3. Final Answer:

   70 -> Option B

4. Quick Check:

   Sum of Banana values = 70 [OK]

Hint: Sum values where criteria matches in same row [OK]

Common Mistakes:

• Summing only first match
• Adding values from wrong rows
• Confusing criteria with sum range

4. You wrote this formula: =SUMIFS(B1:B5, A1:A5, "Apple", C1:C5, ">10")
But column C is empty. What will happen?

medium

A. The formula returns 0 because no rows meet all conditions

B. The formula returns an error because C1:C5 is empty

C. The formula sums all values in B1:B5 ignoring conditions

D. The formula sums values where A1:A5 is "Apple" only

Solution

1. Step 1: Understand the conditions

   The formula sums B1:B5 where A1:A5 = "Apple" AND C1:C5 > 10.

2. Step 2: Check empty C1:C5

   Since C1:C5 is empty, no cells are > 10, so no rows match both conditions.

3. Final Answer:

   The formula returns 0 because no rows meet all conditions -> Option A

4. Quick Check:

   Empty criteria range means no matches, sum = 0 [OK]

Hint: Empty criteria range means no matches, sum is zero [OK]

Common Mistakes:

• Expecting an error from empty range
• Ignoring second condition
• Thinking it sums all "Apple" rows anyway

5. You have this data:

A1:A6 = {"East", "West", "East", "North", "West", "East"}
B1:B6 = {100, 200, 150, 300, 250, 50}
C1:C6 = {"Yes", "No", "Yes", "No", "Yes", "No"}

Which formula sums values in B where region is "East" and status is "Yes"?

hard

A. =SUMIFS(B1:B6, A1:A6, "East")

B. =SUMIF(A1:A6, "East", B1:B6) + SUMIF(C1:C6, "Yes", B1:B6)

C. =SUMIF(B1:B6, "East", C1:C6)

D. =SUMIFS(B1:B6, A1:A6, "East", C1:C6, "Yes")

Solution

1. Step 1: Understand the requirement

   We want to sum B values where A = "East" AND C = "Yes".

2. Step 2: Check each formula

   =SUMIFS(B1:B6, A1:A6, "East", C1:C6, "Yes") uses SUMIFS with both conditions correctly. =SUMIF(A1:A6, "East", B1:B6) + SUMIF(C1:C6, "Yes", B1:B6) sums separately and adds, which is incorrect. =SUMIFS(B1:B6, A1:A6, "East") misses second condition. =SUMIF(B1:B6, "East", C1:C6) has wrong argument order.

3. Final Answer:

   =SUMIFS(B1:B6, A1:A6, "East", C1:C6, "Yes") -> Option D

4. Quick Check:

   SUMIFS sums with multiple conditions [OK]

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

Common Mistakes:

• Adding separate SUMIF results instead of using SUMIFS
• Missing one condition in formula
• Using wrong argument order