Offset, Match Questiond

[BobA]

I borrowed this formula from another worksheet, but I can't get it to work in my new one.

=SUM(OFFSET($F$1,MATCH(0,$F:$F,-1)-(J2),,(J2)))

I want to total the last (x number of) cells that corresponds to cell j2. So if cell J2 has a five in it, then the formula will sum the last five cells in the F column.

(This is the formula I copied from another sheet:
=SUM(OFFSET($AC$1,MATCH(0,$AC:$AC,-1)-(H7),,(H7)))

Thanks, again.

 

[isabelle]

hi,

if you don't want take into account sort order (MATCH -1)
and if there is no empty cell

=SUM(INDIRECT(ADDRESS(COUNTA(F:F)-J2-1,6)&":"&ADDRESS(COUNTA(F:F),6)))

isabelle

Le 2013-07-17 21:20, BobA a écrit :

I borrowed this formula from another worksheet, but I can't get it to work in my new one.

=SUM(OFFSET($F$1,MATCH(0,$F:$F,-1)-(J2),,(J2)))

I want to total the last (x number of) cells that corresponds to cell j2.

So if cell J2 has a five in it, then the formula will sum the last five
cells in the F column.

 

[isabelle]

rectification,

=SUM(INDIRECT(ADDRESS(COUNTA(F:F)-J2+1,6)&":"&ADDRESS(COUNTA(F:F),6)))

isabelle

Le 2013-07-17 23:21, isabelle a écrit :

 

[BobA]

rectification,



=SUM(INDIRECT(ADDRESS(COUNTA(F:F)-J2+1,6)&":"&ADDRESS(COUNTA(F:F),6)))



isabelle



Le 2013-07-17 23:21, isabelle a écrit :

Thanks for the help, but this did not work either. I'm curious why my original formula will work in some columns and some sheets but not others. It seems to be simplistic enough.

 

[isabelle]

hi,

Le 2013-07-18 21:13, BobA a écrit :

Thanks for the help, but this did not work either. I'm curious why my original formula
will work in some columns and some sheets but not others. It seems to be simplistic enough.

you are using the MATCH function in your formula,
these two ranges ( in some columns and some sheets but not others) are
sorted it the same way?

MATCH(lookup_value,lookup_array,match_type)

Match_type is the number -1, 0, or 1. Match_type specifies how
Microsoft Excel matches lookup_value with values in lookup_array.

If match_type is -1, MATCH finds the smallest value that is greater than
or equal to lookup_value. Lookup_array must be placed in descending
order: TRUE, FALSE, Z-A, ...2, 1, 0, -1, -2, ..., and so on.

isabelle

 

[Ron Rosenfeld]

I borrowed this formula from another worksheet, but I can't get it to work in my new one.

=SUM(OFFSET($F$1,MATCH(0,$F:$F,-1)-(J2),,(J2)))

I want to total the last (x number of) cells that corresponds to cell j2. So if cell J2 has a five in it, then the formula will sum the last five cells in the F column.

(This is the formula I copied from another sheet:
=SUM(OFFSET($AC$1,MATCH(0,$AC:$AC,-1)-(H7),,(H7)))

Thanks, again.

Since you don't tell us, I don't know what you mean by "can't get it to work"
Return an error; if so, what is the error?
Returns an unexpected result; if so, what is the original data, the desired result, and the returned result?
Causes Excel to crash; if so, is there an error message?

One problem that might cause it to return an unexpected result would be if there is a zero or negative value in your column. The count would start from that point, using the formula you "borrowed".
If that is the problem, a possible solution might be:

=SUM(OFFSET($F$1,LOOKUP(2,1/ISNUMBER($F:$F),ROW($F:$F))-$J$2,0,$J$2))

If that doesn't work, please answer the above question, and also supply the information requested in my 2nd surmise above.

 

[BobA]

=SUM(OFFSET($F$1,LOOKUP(2,1/ISNUMBER($F:$F),ROW($F:$F))-$J$2,0,$J$2))

I couldn't wrap my head around why my formula would work in some columns but not in others. (#N/A error)

After giving it a lot of thought, I realized the columns that didn't work were the ones that contained a zero or a negative value. I logged in to post my revelatio
and lo and behold I saw your post.

So yes Ron, your formula works perfectly.

Thank you

 

[Ron Rosenfeld]

=SUM(OFFSET($F$1,LOOKUP(2,1/ISNUMBER($F:$F),ROW($F:$F))-$J$2,0,$J$2))

I couldn't wrap my head around why my formula would work in some columns but not in others. (#N/A error)

After giving it a lot of thought, I realized the columns that didn't work were the ones that contained a zero or a negative value. I logged in to post my revelatio
and lo and behold I saw your post.

So yes Ron, your formula works perfectly.

Thank you

Glad to help. Thanks for the feedback.