Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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??? Last edited by bouncebackability : April 9th 08 at 06:18 PM |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting unique values in a list generated with the OFFSET functio | Excel Worksheet Functions | |||
counting the number of equal rows | New Users to Excel | |||
Counting the number of values in a list between two specified valu | Excel Worksheet Functions | |||
How to add equal values and than replace them with their sum? | Excel Worksheet Functions | |||
list values equal in 2 worksheets? | Excel Worksheet Functions |