sumproduct to move columns with weeks

[ALAMZEB]

i am using sumproduct formula (T5) to look customer number and than weeks in row and than taking sum ofplan orders in sheet 2 column I



but i want formula to move with weeks. from current week onward it should sum up colmn I and any weeks in the past it should look in column H, which is confirmed orders





so idea is simple that from current week onwards gives me plan orders and anything past gives me confirmed orders

 

[Hui]

Can you please try this in T5:
=SUMPRODUCT(((Sheet2!$B$3:$B$400=$D5)*(Sheet2!$D$3:$D$400=T$3)*(Sheet2!$I$3:$I$400)))
Copy across and down

Note the two $ which are different to yours

 

[ALAMZEB]

Hi Hui

I want the sumproduct formula to.work with dates.
It should figure out what week is this and than change colums accordingly.

For example

If we are in week 9 than all week before week 9 should pick values from co firmed orders and current week 9 and beyond should pick values from plan

 

[Hui]

Can you please give us a row of answers?
eg Row 15
even as simple as T15:X15

 

[Hui]

T5: =SUMPRODUCT(((Sheet2!$B$3:$B$400=$D5)*(Sheet2!$D$3:$D$400<=T$3)*(Sheet2!$I$3:$I$400)))
copy down and across

 

[ALAMZEB]

Hi Hui



i hope below is much clear



we are in week 11 of 2016. T5 is summing all value from column I of sheet 2.

in sheet 2, we have column H (confirmed orders) and column I (plan).

i want a sumproduct formula in T5 to work with weeks. using weeknum(now()) it should calculate what week are we in and than move colum H and I of sheet accordingly

all weeks before current week (e.g V5 sheet 1 this week is week 11) should sum all values from column H of sheet 2 (in the past)

and all upcoming weeks including current week should sum up all values from column I of sheet 1 (current and future)



i can use sumproduct formula in T5 to work in all cells but dont know how i switch between column H & I of sheet2 acorrding to week number