Ravindra BagaleCourses & study guides मराठी Track your progress

23. Formulas Across Sheets and Workbooks

23.2 Referencing a Normal Range on Another Sheet

By Last updated:

Example first. The ten Module 3 orders sit on the Orders sheet in A1:I11 as a normal range (not a Table). A City Summary sheet must show delivered and total sales per city with SUMIFS that read the Orders sheet.

SUMIFS on a range in another sheet — in 30 seconds

What: Any function can read a range on another sheet: SUMIFS(Orders!$G$2:$G$11, Orders!$C$2:$C$11, A2). Lock the ranges with $ so they do not move when you copy down.

Why: Keeping the report on its own sheet means the raw data can be replaced every month without touching the report formulas.

Tiny example: =SUMIFS(Orders!$G$2:$G$11,Orders!$C$2:$C$11,A2) with Pune in A2 gives ₹544.

First look at the data (before → after):

SUMIFS on a range in another sheet: before and after tables, fictional Blinkit Maharashtra data

Expected result — Sheet City Summary

A: City B: Sales (₹) C: Orders
Pune 544 4
Nashik 360 2
Nagpur 240 1
Kolhapur 232 1
Solapur 1299 1
Sambhaji Nagar 195 1
Total 2870 10
Before table and formula as text

Before — Sheet Orders (a normal range, A1:I11) – columns used

Order ID C: City G: Amount (₹)
BLK-1001 Pune 64
BLK-1002 Nashik 270
AMN-1003 Pune 110
BLK-1004 Nagpur 240
AMN-1005 Kolhapur 232
BLK-1006 Pune 150
BLK-1007 Solapur 1299
AMN-1008 Sambhaji Nagar 195
BLK-1009 Pune 220
BLK-1010 Nashik 90

Formula / click path

City Summary!B2: =SUMIFS(Orders!$G$2:$G$11,Orders!$C$2:$C$11,A2)
City Summary!C2: =COUNTIFS(Orders!$C$2:$C$11,A2)

Do it step by step:

  1. On City Summary, type the six cities in A2:A7.
  2. Click B2, type =SUMIFS( and click the Orders sheet tab.
  3. Select G2:G11, press F4 to make it $G$2:$G$11, and type a comma.
  4. Select C2:C11, press F4, type a comma, click back on City Summary and click A2. Close the bracket and press Enter.
  5. Fill B2 down to B7.
  6. Check: the total of B2:B7 equals =SUM(Orders!G2:G11).

The weak spot of a normal range. Add order BLK-1011 for Pune, ₹300, in row 12 of the Orders sheet. The formula still says $G$2:$G$11, so Pune stays at ₹544 instead of ₹844. Nothing turns red; the number is just wrong.

Three fixes, best first

  1. Turn the Orders data into a Table (next lesson). The reference grows with the data.
  2. Use whole columns: =SUMIFS(Orders!$G:$G,Orders!$C:$C,A2). Simple and safe for SUMIFS, but slower in very large files, and a total typed under the data would be counted twice.
  3. Insert new rows inside the range (above row 11), not below it. Excel then stretches $G$2:$G$11 to $G$2:$G$12. Easy to forget.

Practice task (try it before you open the answer):

  1. Build City Summary with the formulas above.
  2. Add BLK-1011 (Pune, ₹300) in row 12 of Orders and read Pune again.
  3. Change B2 to the whole-column version and read Pune again.
Answer

With $G$2:$G$11 Pune stays ₹544 (row 12 is outside the range). With Orders!$G:$G Pune becomes ₹844.

Ravindra Bagale's Tip

Whenever a total on a summary sheet looks too round or too small, press Ctrl + [ on it (or use Formulas › Trace Precedents) to jump to the cells it really reads.

Loading practice tasks...

  1. Total units (Easy): Total of Orders!D2:D8.
  2. Total value (Easy): Total of Orders!E2:E8.
  3. Count orders (Easy): Count Orders!A2:A8.
  4. Biggest order (Easy): Biggest value in Orders!E2:E8.
  5. North units (Medium): Units for A2 (North).
  6. South value (Medium): Value for A3 (South).
  7. West order count (Medium): How many orders for A4 (West)?
  8. Insulin units (Medium): Units of "Insulin".
  9. North insulin value (Hard): Value for North AND Insulin.
  10. Big South orders (Hard): How many South orders have value over 50000?
  11. North share of value (Hard): North value ÷ total value.

See all practice tasks in the Task Hub →