Working with several sheets
Add up the same cell across three monthly sheets.
Add them one by one, or in one go with a 3D reference: =SUM(Jan:Mar!B2).
Total each region's sales for the quarter in B2:B5: cell B2 to B5 on the Jan, Feb and Mar sheets. Write one formula in B2 and fill it down.
Typing formulas is easiest on a computer with a keyboard.
| A | B | C | |
|---|---|---|---|
| 1 | Region | Q1 total | |
| 2 | West | ||
| 3 | South | ||
| 4 | North | ||
| 5 | East | ||
| 6 | |||
| 7 | |||
| 8 | |||
| 9 | |||
| 10 | |||
| 11 | |||
| 12 |
How SUM works
How do I add up the same cell on several sheets?
Add them one by one, with the sheet name in front of each cell:
=Jan!B2+Feb!B2+Mar!B2. While typing, click each sheet's tab and then the cell, and Excel writes the sheet names for you.What does Jan:Mar!
B2mean?Cell
B2on every sheet from Jan to Mar, including any sheets between them.=SUM(Jan:Mar!B2)adds them all up. This is called a 3D reference.Can I click instead of typing it?
In Excel, type
=SUM(, click the Jan tab, hold Shift and click the Mar tab, then clickB2and press Enter.Which functions work across sheets?
functions such as
SUM,AVERAGE,COUNT,COUNTA,MAXandMIN. Lookups such asVLOOKUP, and functions such asCOUNTIF, give an error with a 3D reference.What if I add another month?
The span takes every sheet between the first and last name, in tab order. Put a new month's sheet between Jan and Mar and it's included. Keep the Summary sheet outside, or it would count itself.
Know someone fighting with a spreadsheet? Send them this page.