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.

3dSum

Posted in Uncategorized

9 thoughts on “Summing in 3D

  1. 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?

  2. 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.

  3. 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

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

  5. 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

  6. 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.

Leave a Reply

Your email address will not be published. Required fields are marked *