Results 1 to 3 of 3
  1. #1
    Lounger
    Join Date
    Oct 2002
    Location
    Mornington Peninsula
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
    Attached Files Attached Files
    Many thanks for any help, much appreciated.
    Have a great day!
    WebMistress

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 Posts
    How about something like:
    =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. #3
    Lounger
    Join Date
    Oct 2002
    Location
    Mornington Peninsula
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you VERY much Steve, that has done the trick.
    Really appreciate your time and have a great day!


    Linda
    Many thanks for any help, much appreciated.
    Have a great day!
    WebMistress

Posting Permissions

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