• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Sum row cells until value is reached

absoftver

New Member
Hello,

I would really appreciate if someone could help me with example attached.

I need formula that sums up values in row (from column A to column G) while max sum (column I) is less or equal to max value (column H).

Thank you,

Petra
 

Attachments

absoftver

New Member
You are amazing! It works great with lookup.

Once again, thank you all very much for your time and help, you saved me lot of nervs. As I am new here, I wonder is it possible to mark this thread as solved for others who might have the same problem?

Petra
 

Asheesh

Excel Ninja
Just do as @Deepak suggested..however, you should know that #N/A is not an error or a mistake as per your statement..

Functions like Lookup, vlookup or Match return #N/A if the value you are searching for is not available within the search range..In simple words, it means the value that you are looking for is not present.
 

absoftver

New Member
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.
 

Attachments

Deepak

Excel Ninja
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.

You misunderstood the column part!
It was used to create a array of 1,2....x!
 

absoftver

New Member
Yes, it works now without mistakes. :) Thank you very much for your time and help, I really appreciate it!

I don't use Excel too often, so I am not familiar with arrays, but will definitely take time to study that area for my future work.

Petra
 

DavidC635

New Member
@Asheesh brilliant. I was trying to use SUM(OFFSET(...)) structure and couldn't get it. :awesome: idea to use SUBTOTAL...

Here is one more:

=LOOKUP(H2, SUBTOTAL(9,OFFSET(A2,,,,COLUMN($A$1:$G$1))))
Hi

is there a way to count the number of cells/coulmns until that criteria is met instead of a sum?

please help
 

DavidC635

New Member
Thank you Bosco, works perfectly.
Is it possible to explain why it works? I like to learn about the combination that is used.

thank you again
 

bosco_yip

Excel Ninja
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
 
Last edited:

Peter Bartholomew

Well-Known Member
Letting the headings be built from the number sequence 'k' and the values (row relative) be 'v' the running total 't' can be expressed
= SUMIFS( v, k, "<="&k )
The maximum value subject to the bound 'b' is 'm' which refers to
= MAX( IF( t<=b, t ) )
Similarly, to count the number of terms 'n' that contribute to the total
= COUNT( IF( t<=b, 1 ) )

Since the formulae are all evaluated as named formula, CSE is not needed and the results are
= m and = n,
copied down.
 

Attachments

Last edited:

Jaff

New Member
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
Is there anyway to get the formula to choose 20? Så the chosen one will be the first number HIGHER than H2?
 

bosco_yip

Excel Ninja
Is there anyway to get the formula to choose 20? Så the chosen one will be the first number HIGHER than H2?
It is a 5 years old thread, please open a new post with attachment and example data as well as the expected result

Regards
 
Top