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

  • Thread starter bouncebackability
  • Start date
B

bouncebackability

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???
 
R

Ron Coderre

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)
 
T

Teethless mama

=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)))
 
B

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
 

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