IF combined with SUMIFS ISSUE

Dears,

I need your help to finish a last detail in my Formula, I have attached you a file sample so you could better evaluate the formula,

In the sheet overview, concerning the yellow line "Production", the formula is as follow for Week 11 :

=IF(OR($A5="";AND(T$2<$A$1;T$1=$B$1));"";IF(AND($D5="Production";T$1<($B$1+2));SUMIFS('Re-work short term'!$K$2:$K$15530;'Re-work short term'!$C$2:$C$15530;$A5;'Re-work short term'!$S$2:$S$15530;T$2);IF(AND($D5="Production";T$1>($B$1+1);$E5="Brand 1");SUMIFS('Pivot DRP'!$C$4:$C$1066;'Pivot DRP'!$A$4:$A$1066;$A5;'Pivot DRP'!$D$4:$D$1066;T$1)/1000;IF(AND($D5="Production";T$1>($B$1+1);$E5<>"Brand 1");SUMIFS('Rework Data DSS'!$F$2:$F$24900;'Rework Data DSS'!$B$2:$B$24900;$A5;'Rework Data DSS'!$D$2:$D$24900;$E5;'Rework Data DSS'!$G$2:$G$24900;T$1))))

The formula is splitted in several part :

1/ when date is earlier than today, it leaves a blank cell,
2/ When date is equal or higher than today it retrieves info from different sources based on the below criterias :
- 2.1/ IF Cell D5 = Production and date is lower than W+2, it has to take from Re-work short term sheet, (based on SKU/date criterias)
- 2.2/ IF Cell D5 = Production and date is higher than W+1 and different from Brand 1, it has to take info from Rework Data DSS Sheet (based on SKU/week#)
- 2.3/ IF Cell D5 = Production and date is higher than W+1 and equals Brand 1, it has to take info from Pivot DRP Sheet (based on SKU/week#)

The formula is working well for point 2.1 & 2.3,
--> However, for point 2.2 I cannot retrieve the info from W11 & 16 from de database 'Rework Data DSS'

I really need your help on this and would be very grateful to solve it.. i am struggling on this for a few days,

Thank you in advance for your help !

Try this formula in T5

Formula:

Please Login or Register  to view this content.

Note: Brand information is in column A :: 'Rework Data DSS'!$A$2:$A$25