I can do this easily with VBA, but wish not to. Also, if at all possible, no array formulas either:
I want to have value in A1 that determines the "number of elements" or "size of range".
If it's 2, I want sum(A3:A4)
If it's 3, I want sum(A3:A5)
If it's 8, I want sum(A3:A10)
I've been messing with offset and index but haven't cracked it. For the above middle example of A1=3:
For starters, I looked at something like offset(A3:A3,A1-1,0) which (unsuitably) is A5:A5, not A3:A5.
I came up with a novel angle, taking the maximum possible range (assume never over 10 elements) and going something like
sum(A3:A12) - sum(A5:A12)
which looks like:
sum(A$3:A$12) - sum(offset(a3:a$12,a1,0))
That seems to work, but that A12 $ anchor is not immune to the offset. It (unsuitably) sums A5:A14.
It's okay only if I ensure that A13:A21 are zero or nonnumeric, a rude restriction.
(A further hokey workaround solution to that is to subtract SUM(A13:A21) but I feel I'm already over my hokey limit, you know?)
Is there something better? Can I "apply an offset to one end of a range" ?
P.S. I can get there handily with a helper column. However I sense that one of you will come up with a "proper" tight answer
Bookmarks