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.

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.

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?

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.

Functions that support 3-D

SUM – adds numbers

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

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

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

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

Answer to EVE’s Question:

You should do your sumif calcs in the individual sheets and then you can do the 3D sum on the cells that contain the sumif calculations.

3D Formula need deeply knowledge.