# question

## Could someone explain the mechanism of this OFFSET formula? Thanks

Hello everyone.

I'm in a lot of confusing....
Although this probably never going to happen in the real scenario, I just curious about its mechanism.

formulas like these below... I'm fully understand about B column, OFFSET will return ranges (A4:A6)(A5:A7), SUM will sum both ranges, and returns an array {12;19}. But column C seriously confuse me.... I just add a SUM on top of it, ideally, I think it would returns a single value 31(sum of 12+19).

Looks like OFFSET actually returns {A4:A5}{A5:A6}{A6:A7}, how could possibly be like this? it's clearly having {2;3} two values, and 3 is the [height] argument.

Thanks for any response.
Original posted in here: https://answers.microsoft.com/en-us/msoffice/forum/all/could-someone-explain-the-mechanism-of-this-offset/0f4c844c-46ea-4636-a7e5-7759eda11940

· 1

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@PowerDekor-2055
I will do some tests on these formulas, if I have some results I would discuss with you.

0 Votes 0 ·

I test on my environment with Microsoft 365 Apps installed, I get following summary, you could have a look.

The formula "OFFSET(A2,{2,3},0,3)" will return 2 three-dimensional arrays from plane data , each area has 3 dimensions. The first formula "{=SUM(OFFSET(A2,{2,3},0,3))}" can sum for each three-dimensional array, then return 2 results. The second formula "{=SUM(SUM(OFFSET(A2,{2,3},0,3)))}" does not seem like the first one, it calculates the data values for each dimension, then return a set of three-dimensional data. To achieve your needs of formula 2, I would suggest you use "=SUM(SUMIF(OFFSET(A2,{2,3},0,3),"<>"))" formula. Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

1.gif (66.0 KiB)
capture25.jpg (34.8 KiB)
capture26.jpg (35.0 KiB)
capture27.jpg (26.9 KiB)
· 2

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks so much for your response.
I noticed that you're using {2,3} rather than my example {2;3}, but that's ok for the point. Also, SUMIF <>0 is a genius method, currently I'm using SUBTOTAL(9,), but I think SUMIF <>0 is better~

As for the mechanism, actually what I'm trying to figure out is why Excel calculates the data values for EACH dimension, clearly SUM(OFFSET()) returns a 1 two-dimensional array, directions depending on {2,3} or {2;3}. But how the extra layer of SUM decomposes that array and returns a 1 three-dimensional array in this case.

Thanks again mate~
@emilyhua-msft

0 Votes 0 ·

Hi @PowerDekor-2055
Yes, the semicolon or comma in the array, this does not affect the use.

why Excel calculates the data values for EACH dimension,

This action is by design,

But how the extra layer of SUM decomposes that array and returns a 1 three-dimensional array in this case.

I think it’s because when two SUM formulas are used consecutively with {}, the internal SUM formula will not be calculated as an array formula, it is recognized as “SUM(OFFSET(A2,{2,3},0,3))” without {}. You could find that as three-dimensional data are placed in one cell, only the top-level data is displayed, and then only 7 is shown on cell.

1 Vote 1 ·
capture30.jpg (26.7 KiB)