• 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

Status
Not open for further replies.
A

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

  • Test.xlsx
    9.2 KB · Views: 512
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
 
Hm, I just found some mistakes. I'm not sure what goes on here. Any suggestions?
 

Attachments

  • Test.xlsx
    10.2 KB · Views: 91
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.
 
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

  • ERV-2015-11-Copy.xlsx
    30.1 KB · Views: 154
absoftver said:
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.
Click to expand...


You misunderstood the column part!
It was used to create a array of 1,2....x!
 
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
 
r1c1 said:
@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))))
Click to expand...

Hi

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

please help
 
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
 
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:
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

  • Running total with bound (PB).xlsx
    17.7 KB · Views: 109
Last edited:
bosco_yip said:
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
Click to expand...

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

Regards
 
bosco_yip said:
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
Click to expand...

Hello, I know it's been a while since this post entry, but in case you can help me, I would appreciate it.

I am trying to reply the formulas from this post entry, but getting different results:

1) The Lookup formula, I am getting =12 (instead of 11)
2) The Match formula, I am getting =2 (instead of 3)

Looks like, in my case, the formula are only considering the first two values. But in yours, it's considering the first 3 values, and I am using your exactly same formula and the same values.

Do you know why could this be happening?

Thank you in advance for your help!
 
edavilac said:
Hello, I know it's been a while since this post entry, but in case you can help me, I would appreciate it.

I am trying to reply the formulas from this post entry, but getting different results:

1) The Lookup formula, I am getting =12 (instead of 11)
2) The Match formula, I am getting =2 (instead of 3)

Looks like, in my case, the formula are only considering the first two values. But in yours, it's considering the first 3 values, and I am using your exactly same formula and the same values.

Do you know why could this be happening?

Thank you in advance for your help!
Click to expand...
This is a 6 years old post.

New question new post. please open a new post with your example data and the expected result

This post closed
 
Status
Not open for further replies.
Back
Top