database design - Excel: Dynamic counter for qualitative data -

i have created workbook used schedule team different job functions on weekly basis. row headers each person's name, , column headers time intervals (however columns half hour , full hour. ex: 8:30 | 9:30 | 10:00 | 11:00). each cell features dropdown of 15 job functions.

i wish create counter each job function related each team member counts how many hours person scheduled function.

i know how use countif function purpose. 1 cell example, be:

=0.5*countif([@[8:30am]],"coffee")+0.5*countif([@[9:00am]],"coffee")+0.5*countif([@[9:30am]],"coffee")+countif([@[10:00am]],"coffee")+countif([@[11:00am]],"coffee")+countif([@[12:00pm]],"coffee")+countif([@[1:00pm]],"coffee")+countif([@[2:00pm]],"coffee")+countif([@[3:00pm]],"coffee")+0.5*countif([@[4:00pm]],"coffee") 

and formula have longer, because want cell count how many times "coffee" comes person in entire week. table has 5 sections of rows each weekday.

but, need way make counter dynamic. schedule changes every week.... how can make counter continue add values in new week when i've cleared old week data? or there way can link table sheet in workbook (making separate table master one) totals numbers found in original counter table every week?

i welcome suggestions or help! in advance

this question can done change original worksheet provided in question, if make every column worth hour or half hour, can count these through simple code -

sub timesheet() dim wrk range dim range dim coffee range dim paperwork range      set wrk = sheets("sheet1").range("b2:i6")      each in wrk      set coffee = range("k" & i.row)     set paperwork = range("l" & i.row)          if = "coffee"             coffee.value = coffee.value + 1         elseif i.value = "paperwork"             paperwork.value = paperwork.value + 1         else          end if      next end sub 

these can changed fit example, need change if sections ("coffee" , "paperwork") equal "coffee" or "paperwork" etc, wrk's range value need changed area of mondays times i.e. e10:v27

please note work if change these every column being equal hour or halfanhour (where instead of if statement adding 1, you'd add 0.5).

hope helps