SUMIF and SUMIFS in Google Sheets - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the SUMIF function do in Google Sheets?

SUMIF adds up numbers in a range that meet a single condition you set. For example, it can add all sales over $100.

beginner

How is SUMIFS different from SUMIF?

SUMIFS adds numbers based on multiple conditions, while SUMIF uses only one condition. Use SUMIFS when you want to check more than one rule.

beginner

Write the syntax of the SUMIF function.

SUMIF(range, criterion, [sum_range])
- range: cells to check the condition
- criterion: the condition to meet
- sum_range: cells to add if condition is true (optional)

intermediate

What happens if you omit the sum_range in SUMIF?

If sum_range is left out, Google Sheets adds the cells in the range that meet the condition instead of a different set of cells.

intermediate

Give an example of a SUMIFS formula with two conditions.

Example: =SUMIFS(C2:C10, A2:A10, ">=2023-01-01", B2:B10, "Apples")
This adds values in C2:C10 where dates in A2:A10 are on or after Jan 1, 2023, and B2:B10 equals "Apples".

What does the formula =SUMIF(A1:A5, ">10") do?

A Adds numbers in A1:A5 greater than 10

B Adds numbers in A1:A5 less than 10

C Counts how many numbers are greater than 10

D Adds all numbers in A1:A5

SUMIF adds only the numbers in the range that are greater than 10.

Which function lets you add numbers based on multiple conditions?

A SUMIF

B COUNTIF

C SUM

D SUMIFS

SUMIFS allows multiple conditions, SUMIF only one.

In SUMIF(range, criterion, sum_range), what is sum_range?

A The cells to add if condition is true

B The condition to meet

C The cells to check the condition

D The total sum of all cells

sum_range is the range of cells to add when the condition is met.

What will =SUMIFS(B2:B10, A2:A10, "=Red", C2:C10, ">5") do?

A Count how many cells are Red

B Add B2:B10 where A2:A10 is Red or C2:C10 is greater than 5

C Add B2:B10 where A2:A10 is Red and C2:C10 is greater than 5

D Add all B2:B10 values

SUMIFS adds values where all conditions are true.

If you want to add sales only for "Apples", which formula is correct?

A =SUMIF(C2:C10, "Apples", B2:B10)

B =SUMIF(B2:B10, "Apples", C2:C10)

C =SUMIF(B2:B10, ">Apples", C2:C10)

D =SUMIF(C2:C10, ">Apples")

You check B2:B10 for "Apples" and sum corresponding C2:C10 values.

Explain how to use SUMIF to add values based on one condition. Include an example.

Think about adding sales only for a specific product.

Describe a situation where SUMIFS is better than SUMIF and how to write the formula.

Consider adding sales for a product in a specific month.

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