Calculate Across multiple Rows Depending on Column Content - end Result Date

Hey there team,

I have a Document here with a special formula in the AD column.

This column Checks the total quantity (column U)'s value depending on the Base Item matching the Item No.

Then is checks the additive quantity on that same Row, then gives the destination column that matches the quantity noted in column AB.

This is a per Item number formula that we were able to get done.

Now what I need help with, is to have the same formula goal in column AC. Difference being, instead of being Based on Item No. which are unique, I need it based on the "Category 2", with this being multiple rows, I assumed sumifs formulas will help to get the totals, but we are trying to see when the quantity will be exhausted.

Example "Drink Mix" category has 3 different lines the total inventory count is 393, counting across each row containing Drink Mix, from left to right, at what row will we finish the 393 cases. So with this example given the, the formula should return "44408".

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

Besides the title, the information in the body of the original post has all the information I believe is needed.

I was not intending to offend the Excel Veteran's by telling them how to do so, I was just explaining it the best way I felt i could get the message of what I was looking for understood.

This proposal employs helper columns (AH:AV) which may be moved and/or hidden for aesthetic purposes.
The formula for the helper columns is: =IF($C6=$C7,"",SUMPRODUCT(($C$4:$C6=$C6)*($F$4:F6)))
The formula for column AC is: =IF(C4=C5,"",INDEX(AH$2:AV$2,AGGREGATE(15,6,(COLUMN(AH4:AV4)-COLUMN(AG4))/(AH4:AV4>=SUMIFS(AB$4:AB4,C$4:C4,C4)),1)))
Let us know if you have any questions.

The formula for the helper columns may be modified to read: =SUMPRODUCT((OrderDemand[[Category 2]:[Category 2]]=$C4)*($F$4:F$81))
The formula for column AC may be modified to read: =IFERROR(INDEX(AH$2:AV$2,AGGREGATE(15,6,(COLUMN(AH4:AV4)-COLUMN(AG4))/(C$4:C$81=C4)/(AH$4:AV$81>=SUMIFS(AB$4:AB$81,C$4:C$81,C4)),1)),"")
Note that the Inventory Count for Drink Mix includes the value 12 in cell AB48 which makes the "Exhaust Date By Category" 44411.
Note that some cells in column AC are blank because some categories do not expire (i.e. CON022) while others contain non numeric values in the Inventory Count (i.e. Special).
On Sheet1 the "Exhaust Date By Category" values are displayed using: =INDEX(MatchRefTable[Exhaust Date By Category],MATCH(A2,OrderDemand[Category 2],0))
Let us know if you have any questions.

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.