# Summing in 3D

Put your red and green glasses away, it’s not that kind of 3D. Three dimensional functions in Excel work across worksheets. Just like you can sum the range A1:A10 without identifying each cell as an argument, you can also sum all the A1’s in all of your sheets.

The syntax for a 3D Sum function is

=SUM(FirstSheet:LastSheet!CellReference)

In this example, the function sums A1 across the sheets Sheet1 – Sheet3. Multiple windows of the same workbook are used so that you can see the values on the different sheets.

Posted in Uncategorized

## 9 thoughts on “Summing in 3D”

1. A fun demonstration is to create the 3-D equations, then go back and rename the worksheets.
The formulas automatically adjust to the new names.

2. eve says:

Hi, thanks for this tip. But I’m a newborn child in Excel and have this problem:
how can I use this 3D counting for SUMIF function?
Is it possible at all?

3. Dick says:

eve: That function doesn’t support 3-D. Search for ‘3-D’ in help to get a list of functions that you can use 3-D with. I’ll post the list when I get a chance.

4. Rich says:

Functions that support 3-D

AVERAGE – calculates average (arithmetic mean) of numbers

AVERAGEA – calculates average (arithmetic mean) of numbers; includes text and logicals

COUNT – counts cells that contain numbers

COUNTA – counts cells that are not empty

MAX – finds largest value in a set of values

MAXA – finds largest value in a set of values; includes text and logicals

MIN – finds smallest value in a set of values

MINA – finds smallest value in a set of values; includes text and logicals

PRODUCT – multiplies numbers

STDEV – calculates standard deviation based on a sample

STDEVA – calculates standard deviation based on a sample; includes text and logicals

STDEVP – calculates standard deviation of an entire population

STDEVPA – calculates standard deviation of an entire population; includes text and logicals

VAR – estimates variance based on a sample

VARA – estimates variance based on a sample; includes text and logicals

VARP – calculates variance for an entire population

VARPA – calculates variance for an entire population; includes text and logicals

5. Kwame says:

Is there a difference between 3-D sum and dsum?

6. Joseph says:

How can I list a specific cell in sheet1 of several books? The list st show the filename and cell value side by side.

7. Timothy says:

I have a question about summing values in different worksheets in Excel. I know that the formula for this is =SUM(FirstSheet:LastSheet!CellReference) which should work with worksheets that are next to each other. But I have a file that has 24 worksheets and I need the totals to add up on the 25th worksheet and I need to pull the numbers from every other worksheet (i.e., worksheet1, worksheet3, worksheet5, worksheet7, etc). Can this be done? I tried the formula mentioned but it highlights the specific cell in all my worksheets. Thanks

8. Lloyd says: