Sum A Cell Through Several Worksheets

March 03, 2021 - by Bill Jelen

Challenge: You have 12 identical worksheets, one for each month. You would like to summarize each worksheet. Is there a better way than using =Jan ! B4 + Feb! B4+Mar! B4+Apr! B4…?

Solution: You can use a 3-D formula such as =SUM(Jan:Dec!B4), as shown in Figure 41.

Figure 41. A 3-D formula adds up all instances of B4 on the 12 sheets from Jan through Dec.

Late-breaking Tip: To add up cell B4 on all the worksheets with Sales in the sheet name, type =SUM(‘*Sales’!B4) and press Enter.

If the first or last worksheet contains a space in the name, you have to use apostrophes around the pair of worksheet names: =SUM(‘Jan 2009: Dec 2009’!B4).

You can easily copy this formula to other cells on the summary worksheet. Gotcha: Do not drag the summary worksheet to appear after the Jan worksheet, or you will set up a circular reference.

Additional Details: It is possible to set up a named range that refers to a 3-D range. Here is an interesting way to set up a named range:

Go to cell B4 on the Jan worksheet.
Select Insert, Name, Define.
The Refers To box contains =Jan!B4. Click in the box. Hold down the Shift key. Click on the Dec worksheet. The Refers To box changes to =Jan:Dec!B4.

Summary: A 3-D formula can sum a specific cell on several worksheets.

Title Photo: Tim Rüßmann at Unsplash.com

This article is an excerpt from Excel Gurus Gone Wild.