Sorry guys, I just can't get it working.
I am sending my original workbook below. On sheet TEST I need to sum up cells from column E to column AI (for each row from row 5 to row 91) while max sum (column AL) is less or equal to max value (column AJ). In some cases it works, in some not. I really don't know what's the catch.
Hi@Asheesh brilliant. I was trying to use SUM(OFFSET(...)) structure and couldn't get it. idea to use SUBTOTAL...
Here is one more:
=LOOKUP(H2, SUBTOTAL(9,OFFSET(A2,,,,COLUMN($A$1:$G$1))))
Try,Hi
is there a way to count the number of cells/coulmns until that criteria is met instead of a sum?
please help
Is there anyway to get the formula to choose 20? Så the chosen one will be the first number HIGHER than H2?Please see that 2 formulas evaluation :
=LOOKUP(H2,SUBTOTAL(9,OFFSET(A2,,,,COLUMN($A$1:$G$1))))
>> =LOOKUP(18,{2,10,11,20,20,25,26})
>> =11
=MATCH(H2,INDEX(SUBTOTAL(9,OFFSET(A2,,,,COLUMN($A$1:$G$1))),0))
>> =MATCH(18,{2,10,11,20,20,25,26})
>> =3
1] The main core formula is SUBTOTAL+OFFSET() which generate an accumulative sum of A2:G2
=SUBTOTAL(9,OFFSET(A2,,,,COLUMN($A$1:$G$1)))
>> ={A2,A2+B2,A2+B2+C2,A2+B2+C2+D2,…......}
>> ={2,2+8,2+8+1,2+8+1+9…......}
>> ={2,10,11,20,20,25,26}
2] The Lookup function based on the criteria, return the closest value (11)
3] The Match function based on the criteria, return the closest value position (3)
Regards
Bosco
It is a 5 years old thread, please open a new post with attachment and example data as well as the expected resultIs there anyway to get the formula to choose 20? Så the chosen one will be the first number HIGHER than H2?