Hi Magnus,
Glad you solved it. So here it goes. Basically I saw you said thar for 001 your formula is working, so I found you are summing the column of consolidated week.
So for week number we reuire the last column where there is a consolidation for various acct's nos.
=SUMPRODUCT(INDEX($B$3:$G$5,,LOOKUP(2,1/(I3=$B$1:$G$1),COLUMN($B$1:$G$1)-COLUMN($B$1)+1)))
So LOOKUP(2,1/(I3=$B$1:$G$1),COLUMN($B$1:$G$1)-COLUMN($B$1)+1) part in INDEX function returns column number with consolidated values.
Now LOOKUP function look's for a value in LOOKUP Vector and return the corresponding result from the result vector.
For e.g. you want to see the value in a vector {1;2;5;7} and return corresponding letter from result vector {"a";"b";"c";"d"}. So If I ask 2 so the result will be b and if I ask 7 the result will be d.
Note for getting a right result the lookup_vector must be sorted in ascending order.
Now comes a twist in LOOKUP function, if the the lookup value is not found in the lookup vector than the function return the value corresponding to the value which is less than the lookup value.
Say for above e.g. if you ask for lookup value 3, now 3 is not available in lookup vector, so the first value which is lesser than 3 i.e.2 the LOOKUP function will return the result corresponding to it i.e. b.
So basically COLUMN($B$1:$G$1)-COLUMN($B$1)+1 this part is the result vector which will generate an array of numbers like this.
COLUMN($B$1:$G$1) - this part gives array {2,3,4,5,6,7}
Now we subtract COLUMN($B$1) i.e. 2 from above vector so our array will become {0,1,2,3,4,5}. Now we will add 1 to this. So our final result vector will be {1,2,3,4,5,6}, which is equal to number of columns in the array B3:G3 of INDEX function.
Now Lookup Vector 1/(I3=$B$1:$G$1) will be 1 divided by an array of TRUE and FALSE so where ever there is TRUE we will get 1 and when it is FALSE we will get #Div/0 error.
So lets say I3 = 002, we will get a lookup vector like {#Div/0,#Div/0,#Div/0,1,1,1} in your file. Now the max number that the lookup vector can have is 1. So we are looking for a number greater than this i.e. 2 you can also use 3. So lookup Vector will return the last number which is less than this, so in all the cases we will get the column number corresponding to the consolidation of the week.
Hope this will solve your doubt, if not, than write back.
Regards,