+ Reply to Thread
Results 1 to 2 of 2

sum cells with text number text

  1. #1
    Forum Contributor
    Join Date
    07-15-2011
    Location
    Whitby, Canada
    MS-Off Ver
    Excel 2010
    Posts
    121

    sum cells with text number text

    I am creating a spreadsheet for payroll that would be huge without the use of text and numerical values together. The purpose of the spreadsheet is to track overtime hours and calculate pay variations. Each cell indicates one of 11 variables for the 'type' of overtime worked (i.e. Reg OT, Stat holiday OT, Acting Captain OT etc.) then the actual number of hours worked, then *sometimes* a code for the pay period. At its most complicated, a cell could look like this:

    APCSTAT 10 LB4 (Acting PC on a STAT for 10 hours submitted late by 4 weeks)

    or as simple as:

    OTSTAT 6

    I can have 11 different columns for each type of overtime, but I need a formula to add up the hours for each code, as well as a way to check for the 'late by' code and add it if necessary....help please anyone?

    ***UPDATE***

    So I have come up with this formula which works for summing the hours worked:

    =SUMPRODUCT(--(TRIM(REPLACE($C$5:$I$5,1,SEARCH(" ",$C$5:$I$5&" "),""))="AC"),--LEFT(0&$C$5:$I$5,SEARCH(" ",$C$5:$I$5&" ")))*5.39

    but I still need to figure out a formula to catch late entries (cells containing LB#) since the above formula does not capture them at all...
    Since this is a new process- I am open to ideas to better track this information, bearing in mind there are aprox. 200 employees and this is a 24/7 operation, so there will be a lot of information
    dummy workbook attached - thanks!
    Attached Files Attached Files
    Last edited by Greed; 08-12-2011 at 09:34 AM. Reason: update + attachment

  2. #2
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,326

    Re: sum cells with text number text

    To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

    If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

    Doing this will ensure you get the result you need!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1