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.