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.