Sum values in one column until values in another column reach a certain value

[C_merr]

I need to get the sum of the prices when the contract is ZS but need the sum per quantity of 10. So in this example, i need to find the sum of first 4 columns because that will make the quantity add up to 10. The problem is, i copy and paste in sets of data where the "ZS" contract will fill at a different number of prices that cause it to add up to a quantity of 10. in the end I am trying to make a calculator to calculate agricultural crush. I have been stuck on this for days, Please Help!


FIllID	CONTRACT	Price	BuySell	OrderType	Quantity
1	ZS	10914	Buy	LMT	1
2	ZS	10926	Buy	LMT	1
3	ZS	10932	Buy	LMT	1
4	ZS	10944	Buy	LMT	7
5	ZL	3186	Sell	LMT	9
6	ZM	3982	Sell	LMT	11
7	ZS	10946	Buy	LMT	10
8	ZL	3186	Sell	LMT	9
9	ZM	3982	Sell	LMT	11

<tbody>
</tbody>

 

[ChrisM]

Well, you could start with a SUMIFS formula, to get the cumulative sum of all ZS's. Assuming your data table above is A1:F10, add this formula in G2, then drag it down to G10:

=SUMIFS(F$2:F2,B$2:B2,"ZS")

So that will give you the rolling sum, now you just need to pick out when it hits 10. If visually searching won't work, you could do this modification:

=If(SUMIFS(F$2:F2,B$2:B2,"ZS") <10 , "", SUMIFS(F$2:F2,B$2:B2,"ZS"))

Now, you can scan down the column, and the FIRST result you see will be your answer (though you will see additional results below that, they will need to be ignored)

 

[C_merr]

Well, you could start with a SUMIFS formula, to get the cumulative sum of all ZS's. Assuming your data table above is A1:F10, add this formula in G2, then drag it down to G10:

=SUMIFS(F$2:F2,B$2:B2,"ZS")

So that will give you the rolling sum, now you just need to pick out when it hits 10. If visually searching won't work, you could do this modification:

=If(SUMIFS(F$2:F2,B$2:B2,"ZS") <10 , "", SUMIFS(F$2:F2,B$2:B2,"ZS"))

Now, you can scan down the column, and the FIRST result you see will be your answer (though you will see additional results below that, they will need to be ignored)

This tells me when the quantity will add up to 10 for ZS, but i need a formula that will get the average price of the "ZS's" each grouping of a quantity of 10. i will then put this number into an equation. and it wont always be the same number of rows that it has to add up when i copy and paste each new order. If this is possible please let me know, Thank you for trying to help!

 

[C_merr]

I want the formula to do something like: If the contract is ZS, return the average price, per 10 contracts filled. if that makes sense?

 

[ChrisM]

You're losing me on the 10 part. Is it 10 ZS contracts, or 10 ZS UNITS, because in your example, the first four ZS row quantity adds up to 10 (1+1+1+7)

Are you saying for every 10 ZS's in column B, you want the average of their quantities in column F?

if it's 10 UNITS, how would you ever guarantee that the quantities add up to even amounts of 10?