Hi Thank you for get back to me. your formulas are much more cleaner. I used the both formulas and they work like a charm. much more appreciated
But in your formulas it only pull the "driver hours". in my raw data there are some employee have hours in other hours row as well. the hours I use in my Calculation sheet should be "Subtotal hours". Could you edit your formulas a bit so I can pull subtotal hours instead of driver hours. and yes i don't use the same reference columns for the hours because I want to caculat the weekly hours which star from Sunday to Sat and it stars again for each one. for example in this work sheet it come across three weeks. I have to calculate them separately which are Tue , Jan 26 to Sat Jan 30 ; Sun Jan 31 to Sat Feb 6; Sun Feb -7 to Wed Feb 10. and when the time period change the date of week in per column changes too. and each time I have to manually change the column reference to caculat different weekly hours. and this is my final issue to find out a formula can sum the weekly hours according to the date of the week.
by the way, I couldn't get:=IFERROR(INDEX(range-with-names,small(if(range-with-dates=cell-with-date,row(range-with-dates)-how-many-rows-in-heading),ROWS($A$1:A1))),"")
entered using CTRL SHIFT ENTER" this formula to work.
Thank you so much for all your help