formula

[Kim]

I am using a microsoft student attendance template and
have converted the data for office employee attendance
sheets. I have five separate code columns at the end of
the attendance sheet representing vacation, sick, comp,
etc. My question is if an employee is asked to fill in
the hours used on vacation (8V, for example), what would
the formula be to calculate all of the vacation time
used for that particular month and placed in the vacation
column at the end of the table? Using the code (V) is
throwing me off in developing the formula. Any
assistance would be appreciated.

 

[Debra Dalgleish]

Use the SUMPRODUCT function. For example, if an employees attendance is
in cells A3:J3, type a "V" in cell K2. Then, enter the following formula
in cell K3:

=SUMPRODUCT((RIGHT($A3:$J3,1)=K2)*(LEFT($A3:$J3,LEN($A3:$J3)-1)))

This assumes a one-letter code for each type of day away.

Copy the formula down to the last row of data.
Enter other codes in row 2, and copy the formula across to the last code.