Multiple condition formula

[Tybudd]

Hey Guys I need Help with Conditional Calculations on my spreadsheet, I think the problem is that the column where the formula needs to be, is also being checked by conditions according to the format. I'd like to explain the goal, show you what I attempted to do, see if you can explain to me why it didn't work, and suggest a correct formula for the goal, been at this for hours....

I have a production Spreadsheet.

• R=Production Order
• Q=Order Sequence
• S=Total Cases Produced in the Production order, can be placed on any row within that particular sequence of a Production Order
• W=Finished Cases

W=Finished Cases is the end Goal Problem Area, if I have a sequence of 4 items for a particular Production Order, the first 3 will be calculated by a formula to yield 95% of the Production Weight, that part is fine, I got that down, but the issue is what is needed with the last Finished Case Cell in the sequence, this cell needs to find the total (S) in relation to the row it is aligned on, then subtract it from the sum of the previous 3 in the sequence to note the remaining difference. Why? If the sheet was stagnant, I could just do a simple =total-sum(w8:w10) and get what I need, but I have to change info a lot, and order of display depending on the cells, so this is what I tried to do for an example to get a better understanding:

=SUMIF(R:R,[@[Production Run]],S:S)-sumifs(W:W,R:R,[@[Production Run]],Q:Q,<[@[Order of run]])

Destination of that formula would be at W10

Everything before the minus is good, I'm not sure if a formula of If(and then sum can work, but hopefully you can see the base logic of the goal.

thanks guys!!

PS tried to upload the file, it only allowed photo

 

[RoryA]

So is R the [Production run] column of your table and Q the [Order of run] column?

 

[Tybudd]

yes

 

[RoryA]

I think what you want is:

IF([@[Order of run]]=MAX(IF([Production Run]=[@[Production Run]],[Order of run])),

which will require array entry (i.e. using Ctrl+Shift+Enter rather than just Enter, unless you have the latest 365 builds).

 

[Tybudd]

Wow, you got it.

Rory admit it, at least I gave you one of your top 3 most complex arguments to figure out?