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. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,202
    Thanks
    14
    Thanked 330 Times in 323 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

  4. #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
  •