Combine team trackers in Google Sheets with IMPORTRANGE and QUERY

The setup this solves

A sales or collections team of four keeps one tracker each, because nobody wants to edit a shared sheet at the same time as five others. Every Monday the manager asks for the open pipeline by owner and client, and someone copies four tabs into a fifth by hand. The combined view should instead update itself, and every source row should be accounted for.

Each tracker in the example has the same five columns in the same order: date, client, stage, value in ₹, and the owner's name typed in column E. Agreeing that column order is the real prerequisite. IMPORTRANGE does not match columns by header; it pastes them in position.

The formula

Put this in cell A1 of a tab called combined in the manager's file:

=QUERY(
  {IMPORTRANGE("https://docs.google.com/spreadsheets/d/<asha-file-id>", "Tracker!A2:E");
   IMPORTRANGE("https://docs.google.com/spreadsheets/d/<vikram-file-id>", "Tracker!A2:E")},
  "select Col5, Col2, sum(Col4)
   where lower(Col3) = 'open' and Col1 >= date '2026-09-01'
   group by Col5, Col2
   order by sum(Col4) desc
   label Col5 'Owner', Col2 'Client', sum(Col4) 'Open pipeline'", 0)

Three details matter. The ranges start at row 2, so headers from each file are not stacked in the middle of the data. Open-ended ranges like A2:E pick up new rows automatically. And because the input is an array rather than a named range, QUERY refuses letters like A or D; it needs Col1 to Col5.

The semicolon between the two IMPORTRANGE calls stacks them vertically. A comma would place them side by side, which is almost never what you want here. In some spreadsheet locales, including ones that use a comma as the decimal separator, the column separator inside braces is a backslash instead; the semicolon for rows stays the same.

Google documents both functions in its help centre: IMPORTRANGE and QUERY.

What a strict query quietly drops

The SQL below runs the same logic as a plain where Col3 = 'Open' query over eight synthetic stacked rows. It is SQLite, not Sheets, but the two failures behave the same way.

-- Synthetic stacked rows, in the same column order as the Sheets formula:
-- Col1 date, Col2 client, Col3 stage, Col4 value (₹), Col5 owner
CREATE TABLE stacked (Col1 TEXT, Col2 TEXT, Col3 TEXT, Col4 REAL, Col5 TEXT);
INSERT INTO stacked VALUES
 ('2026-09-02', 'Kiran Stores',    'Open',   85000, 'Asha'),
 ('2026-09-09', 'Kiran Stores',    'Open',   40000, 'Asha'),
 ('2026-08-28', 'Mehta & Co',      'Open',   60000, 'Asha'),   -- before the date filter
 ('2026-09-15', 'Om Agencies',     'Won',   120000, 'Asha'),
 ('2026-09-04', 'Apex Auto',       'Open',  210000, 'Vikram'),
 ('2026-09-18', 'Delta Parts',     'open',   55000, 'Vikram'), -- lower-case stage
 ('2026-09-21', 'Delta Parts',     'Open',    NULL, 'Vikram'), -- "1,10,000" typed as text, read as null
 ('2026-09-22', 'Vardhman Retail', 'Lost',   30000, 'Vikram');

-- The strict version: exact stage match, as a first-draft QUERY would be written.
SELECT Col5 AS owner, Col2 AS client, SUM(Col4) AS open_pipeline,
       COUNT(*) AS rows_matched, COUNT(Col4) AS rows_with_a_number
FROM stacked
WHERE Col3 = 'Open' AND Col1 >= '2026-09-01'
GROUP BY Col5, Col2
ORDER BY open_pipeline DESC;

Apex Auto and Kiran Stores come out correctly. Delta Parts shows a blank total: its one exact "Open" row had a value typed as text, and the "open" row with a lower-case stage was never selected. Nothing errors. The manager simply sees a smaller pipeline than the team has.

In Sheets the text value is lost for a documented reason. When a column holds mixed types, QUERY treats the majority type as the column's type and turns minority values into nulls. A single "1,10,000" typed as text in a column of numbers disappears from sum(). Stray spaces and inconsistent capitals, on the other hand, are an ordinary string comparison being exact.

Repairs, and a count that proves them

Fix the stage comparison inside the query with lower(Col3) = 'open', as the formula above does. QUERY has no trim function, so trailing spaces are better stopped at the source with data validation: a dropdown list of stages on each person's tracker makes "Open " impossible to type.

For numbers, add data validation on the value column that rejects anything that is not a number, and fix the existing text values once. If you cannot control the sources, stack the ranges on a helper tab, convert the value column there with =ARRAYFORMULA(IF(D2:D="", , VALUE(SUBSTITUTE(D2:D, ",", "")))), and point QUERY at the helper tab instead.

Then check the plumbing. Next to the summary, count rows per source and compare them with the combined tab:

Asha rows     =COUNTA(IMPORTRANGE("<asha-url>", "Tracker!B2:B"))
Vikram rows   =COUNTA(IMPORTRANGE("<vikram-url>", "Tracker!B2:B"))
Combined rows =COUNTA(combined_raw!B2:B)
Check         =IF(Combined_rows = Asha_rows + Vikram_rows, "OK", "ROWS MISSING")

The same stacking, repair and reconciliation can be tested in pandas with synthetic data, which is useful when you want to prove the rules before editing a live sheet:

import pandas as pd

# Synthetic: what each team member's own tracker tab holds.
asha = pd.DataFrame({
    "date": ["2026-09-02", "2026-09-09", "2026-08-28", "2026-09-15"],
    "client": ["Kiran Stores", "Kiran Stores", "Mehta & Co", "Om Agencies"],
    "stage": ["Open", "Open", "Open", "Won"],
    "value": [85000, 40000, 60000, 120000],
})
vikram = pd.DataFrame({
    "date": ["2026-09-04", "2026-09-18", "2026-09-21", "2026-09-22"],
    "client": ["Apex Auto", "Delta Parts", "Delta Parts", "Vardhman Retail"],
    "stage": ["Open", "open", "Open ", "Lost"],
    "value": [210000, 55000, "1,10,000", 30000],   # one value typed as text
})

stacked = pd.concat([asha.assign(owner="Asha"), vikram.assign(owner="Vikram")],
                    ignore_index=True)

# The same repairs the Sheets formula makes: trim/lower the stage, turn text amounts into numbers.
stacked["stage_clean"] = stacked["stage"].str.strip().str.lower()
stacked["value_num"] = pd.to_numeric(stacked["value"].astype(str).str.replace(",", ""),
                                     errors="coerce")
stacked["date"] = pd.to_datetime(stacked["date"])

open_pipe = (stacked[(stacked["stage_clean"] == "open") & (stacked["date"] >= "2026-09-01")]
             .groupby(["owner", "client"], as_index=False)["value_num"].sum()
             .sort_values("value_num", ascending=False))
print(open_pipe.to_string(index=False))

# Tripwires for the combined sheet: every source row arrived, and no amount was dropped.
assert len(stacked) == len(asha) + len(vikram)
assert stacked["value_num"].notna().all(), stacked.loc[stacked["value_num"].isna()]
assert open_pipe["value_num"].sum() == 85000 + 40000 + 210000 + 55000 + 110000
total = int(open_pipe["value_num"].sum())
head, tail = str(total)[:-3], str(total)[-3:]
pairs = [head[max(i - 2, 0):i] for i in range(len(head), 0, -2)][::-1]
print("rows in:", len(stacked), "| open pipeline since 1 Sep: ₹" + ",".join(pairs + [tail]))

The repaired pipeline is ₹5,00,000, compared with ₹3,35,000 from the strict version. The asserts are the point: one confirms that all eight source rows arrived, the other that no amount became blank during conversion.

Things that break this later

IMPORTRANGE also has refresh and size limits, and a large file pulling from many sources can become slow. When a team outgrows this, the next step is a single form or a small database feeding one sheet, rather than more formulas.

Tracker consolidation is often the first reporting job handed to a new analyst, and the habit of reconciling counts before sharing totals applies to every later one. We build that habit in the spreadsheet and SQL modules of our Data Analyst programme, which is taught live online, with optional in-person labs at our Noida centre. For a similar merge done on ERP exports instead of Sheets, see build a monthly MIS pack from ERP dumps with SQLite.