# Thread: SUM cells across columns where letters represents values in lookup table

1. ## SUM cells across columns where letters represents values in lookup table

Hi all,
I have a leave planner with all staff names down columns, then across each row is a letter representing the type of leave each staff member has taken/scheduled throughout the current 6 months.

EG. letter A represents a full day of Annual Leave; Ah represents .5 of a day's Annual Leave. and so on ... see Range called TypeHours on the Key sheet. (Note: Colours are conditional formatting to look pretty - no relevance to formula)

Ultimately I want a cumulative total of how much leave has been taken regardless of the type - using the table typeHours. I cant just use COUNT because that counts a whole day even if the letter shows Ah which only represents a half day.

I need the formula in cells down Column G on the Jul-Dec sheet adding the leave for each record. I think its a cross between vlookup, match & index?? ... but I cant put it all together.

Can anyone help me with a formula that will check the letter, look up the typeHours table and add the relevant values to give me a total leave taken for each staff .... pleeeaase ???

Many thanks

=COUNTA(J5:GK5)-COUNTIF(J5:GK5,"*h")/2

it counts all the leave days, but then subtracts half for the leaves ending in H...

Steve

3. Thank you VERY much Steve, that has done the trick.
Really appreciate your time and have a great day!

Linda

4. ## Great help

Originally Posted by sdckapr
=COUNTA(J5:GK5)-COUNTIF(J5:GK5,"*h")/2

it counts all the leave days, but then subtracts half for the leaves ending in H...

Steve
I bumped into this and greatly help solve my question. thanks to Steve

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•