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.