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.

Practice

1. What does the SUMIF function do in Excel?

easy

A. Adds numbers in a range that meet one condition

B. Adds numbers in multiple ranges without conditions

C. Counts how many cells meet a condition

D. Finds the average of numbers in a range

Solution

1. Step 1: Understand the purpose of SUMIF

   SUMIF adds values only if they meet a single specified condition.

2. Step 2: Compare with other options

   Counting cells or averaging are different functions; SUMIF sums conditionally.

3. Final Answer:

   Adds numbers in a range that meet one 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
• Using SUMIF to average values

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

easy

A. SUMIFS(criteria_range1, criteria1, sum_range)

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

C. SUMIFS(sum_range, criteria1, criteria_range1)

D. SUMIFS(sum_range, criteria_range1, criteria1)

Solution

1. Step 1: Recall SUMIFS syntax

   SUMIFS requires sum_range first, then pairs of criteria_range and criteria.

2. Step 2: Check each option

   Only SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2) follows the correct order: sum_range, criteria_range1, criteria1, etc.

3. Final Answer:

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

4. Quick Check:

   SUMIFS syntax = sum_range + criteria pairs [OK]

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

Common Mistakes:

• Swapping sum_range and criteria_range
• Missing criteria pairs
• Using SUMIF syntax for SUMIFS

3. Given this data:

A1:A5 = {10, 20, 30, 40, 50}
B1:B5 = {"Yes", "No", "Yes", "No", "Yes"}

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

medium

A. 150

B. 100

C. 120

D. 90

Solution

1. Step 1: Identify matching cells in B1:B5

   Cells with "Yes" are B1, B3, B5 corresponding to A1=10, A3=30, A5=50.

2. Step 2: Sum corresponding values in A1:A5

   Sum = 10 + 30 + 50 = 90.

3. Final Answer:

   90 -> Option D

4. Quick Check:

   Sum of A where B = "Yes" is 90 [OK]

Hint: Sum values where criteria matches in criteria_range [OK]

Common Mistakes:

• Summing all values ignoring criteria
• Confusing Yes and No cells
• Using SUM instead of SUMIF

4. You wrote this formula: =SUMIFS(A1:A5, B1:B5, "Yes", C1:C5, ">10") but it returns #VALUE!. What is the likely problem?

medium

A. Sum range and criteria ranges are different sizes

B. SUMIFS cannot handle multiple criteria

C. Criteria ">10" should be in quotes

D. You must use SUMIF, not SUMIFS

Solution

1. Step 1: Check criteria syntax

   Criteria ">10" is correctly in quotes, so no error there.

2. Step 2: Verify range sizes

   SUMIFS requires sum_range and all criteria_ranges to be the same size; mismatch causes #VALUE! error.

3. Final Answer:

   Sum range and criteria ranges are different sizes -> Option A

4. Quick Check:

   Range size mismatch causes SUMIFS to return #VALUE! [OK]

Hint: Ensure all ranges in SUMIFS have same size [OK]

Common Mistakes:

• Forgetting to quote criteria with operators
• Using SUMIF syntax for multiple criteria
• Assuming SUMIFS sums without matching sizes

5. You have sales data:

Product: A2:A7 = {"Pen", "Pen", "Book", "Pen", "Book", "Book"}
Region: B2:B7 = {"East", "West", "East", "East", "West", "East"}
Sales: C2:C7 = {100, 200, 150, 120, 180, 130}

Which formula sums sales of "Pen" in the "East" region?

hard

A. =SUMIFS(A2:A7, C2:C7, "Pen", B2:B7, "East")

B. =SUMIF(A2:A7, "Pen", C2:C7) + SUMIF(B2:B7, "East", C2:C7)

C. =SUMIFS(C2:C7, A2:A7, "Pen", B2:B7, "East")

D. =SUMIF(C2:C7, "Pen", B2:B7)

Solution

1. Step 1: Identify correct function for multiple conditions

   SUMIFS sums with multiple criteria; SUMIF only handles one.

2. Step 2: Check formula arguments

   =SUMIFS(C2:C7, A2:A7, "Pen", B2:B7, "East") correctly uses sum_range C2:C7, criteria ranges A2:A7 and B2:B7 with criteria "Pen" and "East".

3. Final Answer:

   =SUMIFS(C2:C7, A2:A7, "Pen", B2:B7, "East") -> Option C

4. Quick Check:

   SUMIFS with product and region criteria sums sales [OK]

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

Common Mistakes:

• Adding two SUMIF results instead of using SUMIFS
• Mixing sum_range and criteria_range order
• Using SUMIF with multiple criteria