Understanding OFFSET

[rbion]

I was given a solution in a past post that included the OFFSET function in the solution. I've been trying to wrap my head around how it works and I still fall short of the mark. I've read the syntax OFFSET(reference,rows,cols,height,width) and can apply it in its most basic form but the following example leaves me with a gap in my understanding. Any help would be greatly appreciated. Sample attached.

=OFFSET($B$2:$B$5,ROW($B$2:$B$5),,1)

[daddylonglegs]

The ROW part returns an "array" like this

{2;3;4;5}

so in OFFSET that becomes

=OFFSET($B$2:$B$5,{2;3;4;5},,1)

so the OFFSET function returns an array consisting of B2 moved by respectively 2, 3, 4 and 5 rows, i.e.

{"b4";"b5";"b6";"b7"}

Normally that result would need to be used within another function because if you put that in a cell on its own you just get the first value, b4

What are you trying to accomplish?

[daddylonglegs]

OK, I see it's from this formula

=SUMPRODUCT(SUBTOTAL(3,OFFSET($B$2:$B$19,ROW($B$2:$B$19)-MIN(ROW($B$2:$B$19)),,1)),--(LEFT($A$2:$A$19,2)="ON"))

You need to take all this part together....

ROW($B$2:$B$19)-MIN(ROW($B$2:$B$19))

..... that will always return an array beginning with zero, in this case

{0;1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17}

see this link for an explanation

[rbion]

Okay thank you (and thanks for seeing where this post actually originated from - for other readers the full formula referenced by Daddylonglegs came from a post I created called "Using countif after filtering data).

I'll review your information as well as the link as it all looks very promising! Terrific!!