14 Oct 2015

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

Excel 3D Range Review (www.MySpreadsheetLab.com)

 

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!

Post a comment