SUM

Working with several sheets

Spreadsheet in your browser
Lesson concept What you'll do
1 / 7

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).

Typing formulas is easiest on a computer with a keyboard.

A1
ABC
1RegionQ1 total
2West
3South
4North
5East
6
7
8
9
10
11
12
Type to enter a value or a formula starting with =. F2 edits, F4 adds $ to a reference.
Study notes

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!B2 mean?

    Cell B2 on 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 click B2 and press Enter.

  • Which functions work across sheets?

    functions such as SUM, AVERAGE, COUNT, COUNTA, MAX and MIN. Lookups such as VLOOKUP, and functions such as COUNTIF, 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.