SUMIF and SUMIFS in Excel - Step-by-Step Guide

Introduction

SUMIF and SUMIFS help you add numbers that meet certain conditions. They save time by adding only the values you want, like sales from one region or dates after a certain day.

When you want to add sales only from the East region in a sales report.

When you need to sum expenses that are above a certain amount.

When you want to add hours worked only by a specific employee.

When you want to total orders placed in a specific month.

When you want to sum values that meet multiple conditions, like sales from the East region in January.

Steps

Step 1: Click

- cell where you want the result

The cell is selected and ready for formula input

Step 2: Type

- the selected cell

Formula starts with =SUMIF(

💡 Use SUMIF for one condition, SUMIFS for multiple conditions

Step 3: Enter

- the range of cells to check the condition (e.g., A2:A10)

The formula shows the range for the condition

Step 4: Type

- the condition in quotes or cell reference (e.g., "East" or B1)

The formula shows the condition to match

Step 5: Enter

- the range of cells to sum (e.g., B2:B10)

The formula shows the range of numbers to add

Step 6: Press

- Enter key

The cell shows the sum of values that meet the condition

Step 7: For multiple conditions, type

- the formula bar

The formula sums values that meet all listed conditions

Before vs After

Before

A list of sales regions in A2:A10 and sales amounts in B2:B10 with mixed regions

After

A cell shows the total sales amount only for the East region, for example 1500

Settings Reference

Range

📍 First argument in SUMIF or criteria_range arguments in SUMIFS

Cells to check for the condition

Default: No default, user must specify

Criteria

📍 Second argument in SUMIF or criteria arguments in SUMIFS

Condition to decide which cells to include

Default: No default, user must specify

Sum_range

📍 Third argument in SUMIF or first argument in SUMIFS

Cells to add when condition is met

Default: If omitted in SUMIF, range is used

Common Mistakes

Using SUMIF with multiple conditions separated by commas

SUMIF only supports one condition; multiple conditions need SUMIFS

Use SUMIFS for multiple conditions with syntax =SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2)

Not using quotes around text criteria

Text criteria must be in quotes or a cell reference, otherwise formula errors

Write criteria as "East" or use a cell reference like B1

Sum_range and criteria_range have different sizes

Ranges must be the same size or formula returns error

Make sure sum_range and criteria_range cover the same number of rows or columns

Summary

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

Always use quotes for text conditions and ensure ranges are the same size.

Use SUMIFS when you need to check more than one condition at the same time.