|
3D Sum formula
Is it possible to easily add up the same cell across spreadsheets?
By Neale Blackwood, 1 Aug 03
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)
First published: CPA Australian
Magazine August 2003 - Page 64 Excel Yourself
Be the First to Comment!
Related Articles
| 3D SUMIF formula - Is it possible to do a 3D SUMIF? By that I mean to SUM a column of values based on the codes in another column through multiply sheets? |
|
Yes, but
the formula is complicated. Assume three sheets named,
Jul, Aug and Sep ... |
 |
| Multi-criteria SUMIF - I use SUMIF to sum a range based on one condition. Is there a way to sum a range based on two or more conditions? |
|
One way is to use SUMPRODUCT. The following formula will sum the range C1:C10 based on the contents of A1:A10 and B1:B10 ... |
|
| SUM shortcut - Alt key shortcut |
|
Keyboard shortcut for the SUM function ... |
|
|
Print
