Cross-sheet references in Google Sheets - Real Business Scenario

Scenario Mode

👤 Your Role: You are a sales analyst at a retail company.

📋 Request: Your manager wants a summary sheet that shows total sales per product category by month, using data stored in separate monthly sheets.

📊 Data: You have three sheets named 'Jan', 'Feb', and 'Mar'. Each sheet lists sales transactions with columns: Product, Category, and Sales Amount.

🎯 Deliverable: Create a summary sheet that uses cross-sheet references to calculate total sales for each category for January, February, and March.

Sample Data

Product	Category	Sales Amount
Apples	Fruits	100
Bananas	Fruits	150
Carrots	Vegetables	80
Broccoli	Vegetables	120

1

Step 1: Create a new sheet named 'Summary' to hold the total sales per category by month.

No formula needed for this step.

Expected Result

A blank 'Summary' sheet is ready for data.

2

Step 2: In the 'Summary' sheet, list the product categories in column A starting from A2: Fruits, Vegetables.

Enter manually: A2='Fruits', A3='Vegetables'.

Expected Result

Categories 'Fruits' and 'Vegetables' appear in cells A2 and A3.

3

Step 3: In the 'Summary' sheet, add month names in cells B1, C1, and D1: Jan, Feb, Mar.

Enter manually: B1='Jan', C1='Feb', D1='Mar'.

Expected Result

Months 'Jan', 'Feb', and 'Mar' appear in cells B1, C1, and D1.

4

Step 4: Calculate total sales for 'Fruits' in January by summing sales from the 'Jan' sheet where Category is 'Fruits'.

=SUMIF(Jan!B:B, A2, Jan!C:C)

Expected Result

Cell B2 shows 250 (100 + 150).

5

Step 5: Calculate total sales for 'Vegetables' in January similarly.

=SUMIF(Jan!B:B, A3, Jan!C:C)

Expected Result

Cell B3 shows 200 (80 + 120).

6

Step 6: Repeat the SUMIF formula for February sales in column C using the 'Feb' sheet.

In C2: =SUMIF(Feb!B:B, A2, Feb!C:C) In C3: =SUMIF(Feb!B:B, A3, Feb!C:C)

Expected Result

Cells C2 and C3 show total sales for Fruits and Vegetables in February.

7

Step 7: Repeat the SUMIF formula for March sales in column D using the 'Mar' sheet.

In D2: =SUMIF(Mar!B:B, A2, Mar!C:C) In D3: =SUMIF(Mar!B:B, A3, Mar!C:C)

Expected Result

Cells D2 and D3 show total sales for Fruits and Vegetables in March.

8

Step 8: Verify all formulas correctly pull data from the respective sheets and display totals in the 'Summary' sheet.

No new formula; check previous results.

Expected Result

Summary sheet shows total sales per category for each month.

Final Result

Summary Sheet

      |  Jan  |  Feb  |  Mar  
--------------------------------
Fruits   |  250  |  300  |  280  
Vegetables |  200  |  220  |  210  

✓Fruits consistently have higher sales than Vegetables each month.

✓February shows the highest sales for both categories.

Bonus Challenge

Add a total sales column that sums sales across all three months for each category.

Show Hint

Use the SUM function across columns B to D, e.g., =SUM(B2:D2) for Fruits.