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

 

[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