counting the last 3 values not equal to x in a list
 

ok, im in desperate need of help...

i have 20 columns (cells A1 to T1) in these cells the default value is 'x' but these x's change to numerical values when i enter data elsewhere. leaving me with a possible example like this...

12 4 4 11 7 1 1 19 20 6 13 x x x x x x x x x

i want to be able to add up the sum of the last THREE values (on the rightmost side) that arent equal to x

i.e. i want to add 20, 6 and 13

however the position of these last three values can vary between anywhere in these columns although they are always joined (never an x between two values but can be in columns A,B,C or G,H,I or R,S,T)

and i cant change the order the numbers appear.

anyideas what i could enter into cell U1???

counting the last 3 values not equal to x in a list
 

Try something like this:

=SUM(OFFSET(A1,,MATCH("X",A1:T1,0)-4,,3))

Is that something you can work with?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"bouncebackability" wrote in
message ...

ok, im a bit of a predicament

i have 20 columns (cells A1 to T1) in these cells the default value is
'x' but these x's change to numerical values when i enter data
elsewhere. leaving me with a possible example like this...

12 4 4 11 7 1 1 19 20 6 13 x x x x x x x x x

i want to be able to add up the sum of the last THREE values (on the
rightmost side) that arent equal to x

i.e. i want to add 20, 6 and 13

however the position of these last three values can vary between
anywhere in these columns although they are always joined (never an x
between two values but can be in columns A,B,C or G,H,I or R,S,T)

anyideas what i could enter into cell U1???




--
bouncebackability

counting the last 3 values not equal to x in a list
 

=SUM(INDEX(A1:T1,MATCH(LOOKUP(10^10,A1:T1),A1:T1,0 )-2):INDEX(A1:T1,MATCH(LOOKUP(10^10,A1:T1),A1:T1,0)) )


"bouncebackability" wrote:


ok, im a bit of a predicament

i have 20 columns (cells A1 to T1) in these cells the default value is
'x' but these x's change to numerical values when i enter data
elsewhere. leaving me with a possible example like this...

12 4 4 11 7 1 1 19 20 6 13 x x x x x x x x x

i want to be able to add up the sum of the last THREE values (on the
rightmost side) that arent equal to x

i.e. i want to add 20, 6 and 13

however the position of these last three values can vary between
anywhere in these columns although they are always joined (never an x
between two values but can be in columns A,B,C or G,H,I or R,S,T)

anyideas what i could enter into cell U1???




--
bouncebackability

WOW,

ive always considered myself pretty capable at Excel, i can do a vast majority of things. but these two answers baffled me, i used Ron Coderre's suggestion as it was shorter.

and it worked, but i havent got a clue how, lol.

big thanks to both answers :D