3D Array Formulas

Created by Laurent Longre

The problem – to make a 3D worksheet array formula. What this means is to
create an array representing a z-range (a range across worksheets) that
evaluates in the formula bar as an array. The 3D range used in Excel, i.e.

Sheet1:Sheet4!A2:B5

does not behave that way. I suspect that nearly everyone on the EEE list
has tried to do this and found that it was not possible. However, Laurent
found that it was possible, given some advanced formula tricks. The INDIRECT
function can return a 3D reference if it is operated on by the N function.
An illustration of this type of formula is shown below.

3D Diagonal Formula –

=SUM(N(INDIRECT(“Sheet”&{1,2,3}&”!”&ADDRESS({1,2,3},{1,2,3}))))

returns the sum of Sheet1!A1, Sheet2!B2 and Sheet3!C3. How does it work?

“Sheet”&{1,2,3}&”!”&ADDRESS({1,2,3},{1,2,3}) evaluates to the array of
strings.

{“Sheet1!$A$1?,”Sheet2!$B$2?,”Sheet3!$C$3?}

When the INDIRECT function operates on this array, the expected array of
values appear (by highlighting in the formula bar and pressing F9), but
for some reason this array cannot be used by Excel functions. The use of
the N function creates an array that can be used, so that the SUM function
returns the desired result.

Posted in Uncategorized

9 thoughts on “3D Array Formulas

  1. Dick,

    I’ve been coming here for a while and enjoy the readings. I’m not at the level of most of the folks that post here, but I am curious about this one. How is this different than =SUM($A$1)+SUM(Sheet2!$B$1)+SUM(Sheet3!$C$1). Wouldn’t they return the same values?

    I’m probably just not seeing the big picture of the use of this type of formula…

    -Steve

  2. Steve – It’s true that for the example given a simple summation formula does the job, but the point is that this method extends to larger selections.

    TO see this you can replace {1,2,3} by row(1:10) to sum across 10 sheets, or define the name ‘AllSheets’ as ‘=get.workbook(1)’ to refer to all sheets in a workbook.

    This 3D-type functionality also works with other functions than just n and t: subtotal,countif,sumif and database-functions also allow arrays as references.

  3. I’m not quite there. How come =SUM(N(INDIRECT(“Sheet”&”!”&ADDRESS({8,8,8},{7,7,7})&”:”&ADDRESS({9,9,9},{8,8,8})))) does not evaluate to the same as =SUM(Sheet1:Sheet3!G8:H9)? It looks like N is just taking the first element of each range and the sum is operating on those. I’m not having any luch nesting another SUM. Any ideas?

    Brett

  4. =SUM(SUBTOTAL(9,(INDIRECT(“Sheet”&”!”&ADDRESS({8,8,8},{7,7,7})&”:”&ADDRESS({9,9,9},{8,8,8}))))) works. (Thanks, Lori)

    Brett

  5. I am trying to use Excel to sort club officers. Each club gets a sheet. How can I sort and report on the same position across the sheets? I assume this is an array, something I vaguely remember from programming about 30 year ago! Thanks in advance. Michael


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

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