SUMIF and SUMIFS in Google Sheets - Step-by-Step Guide

Introduction

SUMIF and SUMIFS help you add numbers in a list only when certain conditions are true. This is useful when you want to total sales, expenses, or any numbers but only for specific items or dates.

When you want to add sales amounts only for a specific product.

When you need to total expenses that happened in a certain month.

When you want to sum hours worked by a particular employee.

When you want to add numbers that meet multiple conditions, like sales for a product in a specific region.

When you want to quickly get totals without sorting or filtering your data.

Steps

Step 1: Click

- a blank cell where you want the total to appear

The cell is selected and ready for typing a formula

Step 2: Type

- the selected cell

You start entering the formula

💡 Start with =SUMIF( for one condition or =SUMIFS( for multiple conditions

Step 3: Enter

- the formula

The formula calculates and shows the total based on your conditions

💡 For SUMIF, use syntax: =SUMIF(range, criterion, sum_range). For SUMIFS, use: =SUMIFS(sum_range, criteria_range1, criterion1, [criteria_range2, criterion2], ...)

Step 4: Press

- Enter key

The formula result appears in the cell showing the sum that matches your conditions

Step 5: Check

- your data and formula

You confirm the total matches the expected sum for your conditions

Before vs After

Before

A list of sales with products in column A and amounts in column B, no totals shown

After

A cell shows the total sales amount only for the product 'Apples' using =SUMIF(A2:A10, "Apples", B2:B10)

Settings Reference

range

📍 first argument in SUMIF or criteria_range in SUMIFS formula

The cells to check for the condition

Default: none

criterion

📍 second argument in SUMIF or criterion in SUMIFS formula

The condition that decides which cells to include

Default: none

sum_range

📍 third argument in SUMIF or first argument in SUMIFS formula

The cells with numbers to add

Default: same as range if omitted in SUMIF

Common Mistakes

Using SUMIF with multiple conditions separated by commas

SUMIF only supports one condition; multiple conditions require SUMIFS

Use SUMIFS with syntax =SUMIFS(sum_range, criteria_range1, criterion1, criteria_range2, criterion2)

Mixing up the order of arguments in SUMIFS

SUMIFS requires sum_range first, then pairs of criteria ranges and criteria

Write SUMIFS as =SUMIFS(sum_range, criteria_range1, criterion1, ...)

Not using quotes around text criteria

Text criteria must be in quotes to be recognized correctly

Write text criteria like "Apples" or expressions like ">50" in quotes

Summary

SUMIF adds numbers based on one condition; SUMIFS adds numbers based on multiple conditions.

Always check the order of arguments and use quotes for text conditions.

These formulas help you quickly total data without sorting or filtering.

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