Offset, Match Questiond


B

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.
 
Ad

Advertisements

I

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.
 
I

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 :
 
B

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.
 
I

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
 
Ad

Advertisements

B

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
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top