# 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

• 11.5 KB Views: 7
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))

#### Attachments

• 12.8 KB Views: 5

#### Kayt

##### New Member
@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

• 13.6 KB Views: 6
Last edited: