• 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.

How to sum values just over a quantity we want

Status
Not open for further replies.

Kayt

New Member
I want to find a formula to sum up a column equal or over a specific value.

For example:
I have a list of products with quantities and prices (Row 8 to Row 40)

For Product A, I want to sum up enough Quantities in the list that would equal 30 or 1 item more than 30 quantities.
- Product A criteria is 30qty. The result for I want would be 30 qty (sumB9:B12), $65 (sumC9:C12)
- Product B criteria is 13qty. The result for I want would be 17qty and $22)

I was thinking using a sumif formula..

Can someone help?
Thanks
 

Attachments

  • Book1.xlsx
    11.5 KB · Views: 7
Last edited:
Try,

In G3, array formula (CSE) formula copied across and down :

=SUM(OFFSET(B$8,MATCH($A3,$A$9:$A$100,0),,MATCH($B3-1,SUMIF(OFFSET(INDEX($B$9:$B$100,MATCH($A3,$A$9:$A$100,0)),,,ROW($1:$12),),"<>"))+1))

72950
 

Attachments

  • Accum sum.xlsx
    12.8 KB · Views: 6
@bosco_yip
I tried your formula, it works fine. But what if the data wasn't listed in order?

I have changed the order of Row 8:40
 

Attachments

  • Accum sum.xlsx
    13.6 KB · Views: 7
Last edited:
Please try at G3

=SUMPRODUCT(($A$9:$A$40=$A3)*(ROW($A$9:$A$40)-ROW($A$8)<=MATCH(TRUE,MMULT(N(ROW($B$9:$B$40)>=TRANSPOSE(ROW($B$9:$B$40))),$B$9:$B$40*($A$9:$A$40=$A3))>=$B3,)),B$9:B$40)

Ctrl+Shift+Enter

No idea how you get 40 from Min 23
 

Attachments

  • Accum sum.xlsx
    13.5 KB · Views: 6
Hi, i need help. I dont know how to sum in excel until reach some value? and that stop in coloum?
I attach the file below
 

Attachments

  • Dashboard Parts_Summary_Table 1 Jan - 3 Mei 2021.xlsx
    51.5 KB · Views: 1
Hi, i need help. I dont know how to sum in excel until reach some value? and that stop in coloum?
I attach the file below
Please don't hijack the other person's post.

Open a new post for your own question and together with the attachment

This post is closed.
 
Last edited:
Status
Not open for further replies.
Back
Top