3D Sum Formula

// 3D Sum Formula

3D Sum formula

Is it possible to easily add up the same cell across spreadsheets?

By Neale Blackwood

Yes, these are called 3D formulae. To create a 3D sum formula that sums all the A4 cells in multiple sheets, open a new workbook, click the AutoSum toolbar icon, then click the Sheet2 tab, then click the A4 cell, then hold the Shift key down and click the Sheet3 tab, release the Shift key and press Enter.

The formula will look like this:

=SUM(Sheet2:Sheet3!A4)

You can also select ranges using this method. The formula can be typed, but the mouse method is better. Be wary of using this method if you move sheets around the workbook. In the above example, if Sheet2 was moved to the right of Sheet3, the formula will change to exclude Sheet2.

The above formula can be read as sum of all the A4 cells between Sheet2 on the left and Sheet3 on the right. In other words, moving sheets around can have an impact on 3D formulae. The sheet number makes no difference to the formulae, it is their relative position that decides which sheets are included in the calculation.

One 3D technique uses the two sheet names First and Last. Then the sheets to be included are moved to the right of First and the left of Last. The formula would look like this:

=SUM(First:Last!A4)

2017-03-08T03:53:56+00:00 Functions|