You have one tab per month, per client or per region, each laid out
the same way, and you want a workbook total — the sum of
B2:B50 on all of them. Excel does this with a 3D
reference; Google Sheets has no such syntax. You have three options.
Method 1 — List the sheets by hand
No setup, but you maintain the list yourself.
- Add a tab and the formula silently ignores it until you edit the list.
- A sheet name with a space or symbol needs quotes:
'Q1 2026'!B2:B50. INDIRECTis volatile — on a big workbook it recalculates constantly and slows the file.- Rename a tab and every reference to it breaks with
#REF!.
Method 2 — A custom function you paste yourself
Free and it stays in the sheet — but you also own the upkeep, and this snippet only sums. Open Extensions → Apps Script, paste this, and save:
- Put the formula on a summary tab and pass that tab’s name as
exclude, or it sums its own cell and you get a circular result. - A custom function only re-runs when one of its arguments changes — editing a value on another sheet does not refresh it on its own.
- Counting, averaging or matching a specific value each means another function.
- The script lives in this workbook only. Every other file you want it in, you paste it again and re-authorise; every collaborator does the same in their copy.
Method 3 — The Custom Count & Sum add-on
Install once and the functions are in every spreadsheet on your account — now and later — with the edge cases handled and no code to keep working. After installing Custom Count & Sum:
What the add-on version adds:
- new tabs are included automatically — no list to maintain;
- an
excludedargument that takes one name or an array —=SUMALLSHEETS("B2:B50", {"Summary","Notes"}); - the matching count, average, min, max and median variants;
- a refresh so the result stays current when data on another sheet changes.
The full list is on the function reference.
"Blad1" or
"Feuille1", not "Sheet1". And always exclude
the tab the formula sits on.
Which method should you use?
| Manual list | Own script | Add-on | |
|---|---|---|---|
| Setup | Type every sheet | Paste & maintain code | One-click install |
| New tabs picked up automatically | No | Yes | Yes |
| Survives a tab rename | No (#REF!) |
Yes | Yes |
| Count / average / match a value | More formulas | A function each | Built in |
| Available in your other spreadsheets | — | No — paste per file | Yes — every sheet on your account |
| Upkeep | Edit the list by hand | You own every fix & addition | Updates itself |
| Cost | Free | Free | Free up to 30 cells per formula, then a plan |
A hand-written list is fine for three fixed tabs that never change. A custom function works if the set of sheets grows and you don’t mind maintaining a bit of code. The add-on is the choice when tabs come and go and you need more than a plain sum.
Total every tab with one formula
Custom Count & Sum adds 38 functions to Google Sheets, including
SUMALLSHEETS and COUNTALLSHEETS. Free for
formulas up to 30 cells.