• 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

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

Last edited:

bosco_yip

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

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

Top