Excel’s 3D formulas allow you to work with values from the same range in different sheets. However, not all Excel functions work with 3D formulas. Read my post to see how they work, how to go beyond 3D formulas and how to often avoid complex formulas with proper spreadsheet design!
Review of Excel’s Nifty 3D Range Formula
Don’t forget that you can change the COUNT function to others functions like AVERAGE or SUM.
Below, you’ll see the functions that are compatible with 3D ranges.
3D Range Compatible Functions
Once you understand the structure of how 3D formulas work (watch video below) it’s important to know which Excel functions are compatible with the 3D range functionality.
Here is a list of compatible Excel functions:
AND AVEDEV AVERAGE AVERAGEA COUNT COUNTA DEVSQ LARGE MAX MAXA MEDIAN MIN MINA OR PERCENTILE PRODUCT QUARTILE RANK SKEW SMALL STDEV STDEVA STDEVP STDEVPA SUM SUMSQ TRIM VAR VARA VARP VARPA
Here are some examples:
- =COUNT(‘Month 1:Month 4’!B6:B11)
- =SUM(‘Month 1:Month 4’!B6:B11)
- =AVERAGE(‘Month 1:Month 4’!B6:B11)
What About Countif, Countifs, Sumif, Sumifs, etc?
Functions that are not listed above in blue are not compatible with 3D range formulas. However, it’s possible to work with data across sheets with complex formulas.
Here are some examples:
- =SUMPRODUCT(COUNTIF(INDIRECT(“‘”&SheetList&”‘!B6:B11″),”>”&G7))
- =SUMPRODUCT(SUMIF(INDIRECT(“‘”&SheetList&”‘!B6:B11″),”>”&G7))
- =SUMPRODUCT(COUNTIFS(INDIRECT(“‘”&SheetList&”‘!a6:a14″),”=”&”Sales”,INDIRECT(“‘”&SheetList&”‘!B6:B14″),”>”&G7))
How Do These Formulas Work?
Yes, these formulas are definitely more complex are require a time investment to understand and implement in your spreadsheets. Download my Excel file and watch my YouTube video to learn more.
Is There an Easier Method?
You can often avoid a lot of complex formulas if you spend more time up front designing your spreadsheet. Download my Excel file and go to sheet ‘In a perfect world!’. If all the fields are in separate columns in only 1 sheet then it’s much easier to analyze your data with pivots and formulas. This is ideally how data should be stored in a spreadsheet.
When a single data set is chopped up and spread out in different sheets then complex formulas are often required to pull things back together. So, the choice is yours…you can get started quickly and pay the cost later or spend additional time up front designing the layout of your model and save time in the long run. Yes, it’s definitely possible to have different sheets of data but ideally the sheets should be independent data sets.
Download my Excel file
Download here or via my OneDrive (file 00139)
Watch my YouTube video
Learn More
- While writing this post I noticed that Hui over at Chandoo.org wrote a post about finding the max or min value across sheets. There are also some comments below the post with alternative solutions that don’t use the volatile Indirect function.
- Read this detailed article from Microsoft about 3D ranges.
Subscribe to my YouTube channel and learn more!
About Me
My name is Kevin Lehrbass. I’m a Data Analyst in Toronto. Since 2001 I’ve worked as a Data Analyst and Technical Administrator.
=REPT(SUBSTITUTE(“yada”,”yada”,”data “),3)
Check out my recommend Excel Training!