Karen Menzie
New Member
Hi,
Any help much appreciated:
I am looking for a formula to vlook-up an employee name, and return the amount of times an H or S (holiday or sick) day occurs in their row within a calendar month.
I have a spreadsheet for employee leave, for approx 80 employees, for all of 2015 on one worksheet.
Names go down in column A, the dates go across column after column, and in the cells an H appears for Holiday, and an S for Sick.
NB: I have 2x columns per date because employees can take half days so there is an am/pm column.
My chart runs fluently for 365 days in columns so I do not want to total the month's H or S occurrences in a 'totals' column at the end of each month.( i.e. if I am looking at Feb, and 28th Feb is column BG, I don't want column BH to be a "Feb totals" column because it will be my March 1st column).
This spreadsheet has to be printed in B&W and read by dinosaurs who won't cope with radical changes to its current format/layout.
Underneath each month, I am displaying totals the holidays and sick days for each employee.
I am currently using a Countif formula to calculate the amount of H or S occurrences in each employee's row for the month, but I would prefer a vlookup so that it will accommodate changes/movement in the order and placing of employee names in column A as people come, go or change department.
I've uploaded a much-simplified version of my real file in the hope of making what I am after clearer, something along the lines of:
I want to know Andrew Anderson's total amount of "H" entries in Feb 1st-28th 2015.
So some combination of:
=Vlookup(A6----
Where A6 = Andrew Anderson
And then Countif the number of "H"s in his row for Feb 1st to Feb 28th
Thanks for looking!
Karen
Any help much appreciated:
I am looking for a formula to vlook-up an employee name, and return the amount of times an H or S (holiday or sick) day occurs in their row within a calendar month.
I have a spreadsheet for employee leave, for approx 80 employees, for all of 2015 on one worksheet.
Names go down in column A, the dates go across column after column, and in the cells an H appears for Holiday, and an S for Sick.
NB: I have 2x columns per date because employees can take half days so there is an am/pm column.
My chart runs fluently for 365 days in columns so I do not want to total the month's H or S occurrences in a 'totals' column at the end of each month.( i.e. if I am looking at Feb, and 28th Feb is column BG, I don't want column BH to be a "Feb totals" column because it will be my March 1st column).
This spreadsheet has to be printed in B&W and read by dinosaurs who won't cope with radical changes to its current format/layout.
Underneath each month, I am displaying totals the holidays and sick days for each employee.
I am currently using a Countif formula to calculate the amount of H or S occurrences in each employee's row for the month, but I would prefer a vlookup so that it will accommodate changes/movement in the order and placing of employee names in column A as people come, go or change department.
I've uploaded a much-simplified version of my real file in the hope of making what I am after clearer, something along the lines of:
I want to know Andrew Anderson's total amount of "H" entries in Feb 1st-28th 2015.
So some combination of:
=Vlookup(A6----
Where A6 = Andrew Anderson
And then Countif the number of "H"s in his row for Feb 1st to Feb 28th
Thanks for looking!
Karen
