Consolidating data from multiple sheets in Excel - Cell-by-Cell Formula Trace

Sample Data

Two sheets (Sheet1 and Sheet2) have sales data for products. The Summary sheet lists products to consolidate total sales from both sheets.

Cell	Value
Sheet1!A1	Product
Sheet1!B1	Sales
Sheet1!A2	Apples
Sheet1!B2	100
Sheet1!A3	Bananas
Sheet1!B3	150
Sheet2!A1	Product
Sheet2!B1	Sales
Sheet2!A2	Apples
Sheet2!B2	200
Sheet2!A3	Oranges
Sheet2!B3	120
Summary!A1	Product
Summary!B1	Total Sales
Summary!A2	Apples
Summary!A3	Bananas
Summary!A4	Oranges

Formula Trace

=SUMIF(Sheet1!A:A, A2, Sheet1!B:B) + SUMIF(Sheet2!A:A, A2, Sheet2!B:B)

Step 1: SUMIF(Sheet1!A:A, A2, Sheet1!B:B)

Step 2: SUMIF(Sheet2!A:A, A2, Sheet2!B:B)

Step 3: 100 + 200

Cell Reference Map

Summary Sheet:
+----+---------+
| A  | B       |
+----+---------+
| 1  | Product | Total Sales
| 2  | Apples  | <- formula here
| 3  | Bananas |
| 4  | Oranges |

References:
- Sheet1!A:A and Sheet1!B:B for product and sales
- Sheet2!A:A and Sheet2!B:B for product and sales
- Summary!A2 for product name to match

The formula in Summary!B2 uses product name in Summary!A2 to find matching sales in Sheet1 and Sheet2 columns A and B.

Result

Summary Sheet after formula in B2:
+---------+-------------+
| Product | Total Sales |
+---------+-------------+
| Apples  | 300         |
| Bananas | 150         |
| Oranges | 120         |

The total sales for each product are summed from both sheets and shown in Summary sheet column B.

Sheet Trace Quiz - 3 Questions

What does SUMIF(Sheet1!A:A, A2, Sheet1!B:B) do in this formula?

A Sums sales in Sheet1 for the product named in Summary!A2

B Counts how many times the product appears in Sheet1

C Adds all sales in Sheet1 column B

D Finds the maximum sales value in Sheet1

Key Result

SUMIF(range_to_check, criteria, range_to_sum) + SUMIF(another_range_to_check, criteria, another_range_to_sum) sums matching values from multiple sheets.