Use Sumproduct to flag

[DerekWooley]

I am trying to see if there is a way to flag instances where a start date (Col E) is greater than that of a previous Machine Run. I have two items (29382 and 9384). The projected stock column is adding the machine run qty to current stock in the following machine run to I can see how much I will have. I have a column that marks the runs as preferred for each item on each machine in Col F. For Item 9384, it works perfectly and adds the mach run Qty across both machines for all 3 of the projected runs because the start date is always getting higher. For Item 29382, the 2^nd projected run has a start date that is higher than the 3^rd run so it does not add the run qty.

Below is my data table and the formula I am using for my projected stock.

The formula is looking for items that are “not preferred” on a certain machine and adding the run qty of a “preferred” to the stock of the future run. I would like to not even have to create a flag and just have the formula add the last run qty on the “preferred” machine to the first run on the “not preferred” machine and then add to the later runs on the not preferred machines whenever I create a new row or run. I am not sure if that is possible so I can settle for flagging the instances that have dates that are not less than a run on another machine.

Formula:
=IF(F3="Not Preferred",B3+SUMPRODUCT(--($A$3:$A$10=A3),--($D$3:$D$10=D3),--($E$3:$E$10<E3),$G$3:$G$10)+SUMPRODUCT(--($A$3:$A$10=A3),--($F$3:$F$10="Preferred"),--($E$3:$E$10<E3),$G$3:$G$10),B3+SUMPRODUCT(--($A$3:$A$10=A3),--($D$3:$D$10=D3),--($E$3:$E$10<E3),$G$3:$G$10))


[TABLE="width: 664"]
<tbody>[TR]
[TD]Col A
[/TD]
[TD]Col B
[/TD]
[TD]Col C
[/TD]
[TD]Col D
[/TD]
[TD]Col E
[/TD]
[TD]Col F
[/TD]
[TD]Col G
[/TD]
[/TR]
[TR]
[TD]Item No.
[/TD]
[TD]CUR STK
[/TD]
[TD]Projected Stock
[/TD]
[TD]Machine
[/TD]
[TD]Start Date
[/TD]
[TD]Preferred Machine
[/TD]
[TD]Mach Run Qty
[/TD]
[/TR]
[TR]
[TD]29382
[/TD]
[TD]2500
[/TD]
[TD]2500
[/TD]
[TD]Mach 1
[/TD]
[TD]6/29/2018
[/TD]
[TD]Preferred
[/TD]
[TD]11,000
[/TD]
[/TR]
[TR]
[TD]29382
[/TD]
[TD]2500
[/TD]
[TD]13500
[/TD]
[TD]Mach 1
[/TD]
[TD]2/23/2019
[/TD]
[TD]Preferred
[/TD]
[TD]17,000
[/TD]
[/TR]
[TR]
[TD]29382
[/TD]
[TD]2500
[/TD]
[TD]13500
[/TD]
[TD]Mach 2
[/TD]
[TD]1/11/2019
[/TD]
[TD]Not Preferred
[/TD]
[TD]7,000
[/TD]
[/TR]
[TR]
[TD]29382
[/TD]
[TD]2500
[/TD]
[TD]37500
[/TD]
[TD]Mach 2
[/TD]
[TD]1/14/2020
[/TD]
[TD]Not Preferred
[/TD]
[TD]8,000
[/TD]
[/TR]
[TR]
[TD]9384
[/TD]
[TD]4500
[/TD]
[TD]4500
[/TD]
[TD]Mach 1
[/TD]
[TD]6/5/2018
[/TD]
[TD]Preferred
[/TD]
[TD]30,000
[/TD]
[/TR]
[TR]
[TD]9384
[/TD]
[TD]4500
[/TD]
[TD]34500
[/TD]
[TD]Mach 1
[/TD]
[TD]8/8/2019
[/TD]
[TD]Preferred
[/TD]
[TD]50,000
[/TD]
[/TR]
[TR]
[TD]9384
[/TD]
[TD]4500
[/TD]
[TD]84500
[/TD]
[TD]Mach 2
[/TD]
[TD]11/3/2019
[/TD]
[TD]Not Preferred
[/TD]
[TD]27,000
[/TD]
[/TR]
[TR]
[TD]9384
[/TD]
[TD]4500
[/TD]
[TD]111500
[/TD]
[TD]Mach 2
[/TD]
[TD]1/4/2020
[/TD]
[TD]Not Preferred
[/TD]
[TD]27,000
[/TD]
[/TR]
</tbody>[/TABLE]


Thank you for any help.