SUMIF and SUMIFS in Excel - Cell-by-Cell Formula Trace

Sample Data

Sales data for products with their categories and sales amounts.

Cell	Value
A1	Product
B1	Category
C1	Sales
A2	Apple
B2	Fruit
C2	100
A3	Banana
B3	Fruit
C3	150
A4	Carrot
B4	Vegetable
C4	80
A5	Broccoli
B5	Vegetable
C5	120
A6	Orange
B6	Fruit
C6	90

Formula Trace

=SUMIF(B2:B6, "Fruit", C2:C6)

Step 1: B2:B6 = {"Fruit", "Fruit", "Vegetable", "Vegetable", "Fruit"}

Step 2: Criteria "Fruit" applied to B2:B6

Step 3: C2:C6 = {100, 150, 80, 120, 90}

Step 4: Sum values in C2:C6 where B2:B6 is "Fruit"

Cell Reference Map

    A       B          C
1 Product  Category   Sales
2 Apple    Fruit  ---> 100
3 Banana   Fruit  ---> 150
4 Carrot   Vegetable 80
5 Broccoli Vegetable 120
6 Orange   Fruit  ---> 90

Arrows show which sales values are summed based on category "Fruit" in column B.

The formula checks categories in B2:B6 and sums corresponding sales in C2:C6 where category is "Fruit".

Result

    A       B          C          D
1 Product  Category   Sales      Result
2 Apple    Fruit      100        
3 Banana   Fruit      150        
4 Carrot   Vegetable  80         
5 Broccoli Vegetable  120        
6 Orange   Fruit      90         
7                        =SUMIF(B2:B6, "Fruit", C2:C6) => 340

The formula in D7 sums sales for all products in the Fruit category, resulting in 340.

Sheet Trace Quiz - 3 Questions

What does the formula =SUMIF(B2:B6, "Fruit", C2:C6) do?

A Adds sales amounts for products in the Fruit category

B Adds all sales amounts regardless of category

C Counts how many products are in the Fruit category

D Adds sales amounts for products not in the Fruit category

Key Result

SUMIF sums values in one range based on a single condition applied to another range.

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