+ Reply to Thread
Results 1 to 4 of 4

Formula to total sum of cell values when cells contain both text & numbers

  1. #1
    Registered User
    Join Date
    04-10-2015
    Location
    IOM, Uniten=d Kingdom
    MS-Off Ver
    2010
    Posts
    4

    Formula to total sum of cell values when cells contain both text & numbers

    I have a speadsheet to keep a track of staff shift patterns and leave periods. A number of the cells contain text & numbers and I want to total the sum of the number values that contain certain text. I have attached a sample of the spreadsheet. I have used the array formula {=SUM(IF(LEFT(D6:U6)="A", --RIGHT(D6:U6,LEN(D6:U6)-1)))} in cell Y6 to calculate the number of hours annual leave taken in ROW6 and this formula works fine. However if I use the array formula {=SUM(IF(LEFT(D6:U6)="LD", --RIGHT(D6:U6,LEN(D6:U6)-1)))} in cell AG6 to calculate the number of hours lieu days taken the result is remaining as 0 instead of 12. I get the same result using the array formula {=SUM(IF(LEFT(D6:U6)="PS",--RIGHT(D6:U6,LEN(D6:U6)-1)))} in cell AI6 to calculate the number of hours paid special leave taken.

    Can anybody suggest a solution?
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Office 2010
    Posts
    27,159

    Re: Formula to total sum of cell values when cells contain both text & numbers

    In AG6

    =SUM(IF(LEFT(D6:U6,2)="LD",--RIGHT(D6:U6,LEN(D6:U6)-2),0))

    Same correction for any 2 character checks e.g. "PS"

  3. #3
    Registered User
    Join Date
    04-10-2015
    Location
    IOM, Uniten=d Kingdom
    MS-Off Ver
    2010
    Posts
    4

    Re: Formula to total sum of cell values when cells contain both text & numbers

    Thanks for the solution, works exactly as I wanted.

    Many thanks for your help.

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Office 2010
    Posts
    27,159

    Re: Formula to total sum of cell values when cells contain both text & numbers

    Thanks for the feedback.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Need formula for counting total Numbers in One Cell
    By pvsvprasad in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 08-30-2016, 06:51 AM
  2. Replies: 1
    Last Post: 05-14-2015, 09:27 AM
  3. Replies: 1
    Last Post: 03-28-2015, 03:14 PM
  4. Replies: 4
    Last Post: 07-21-2014, 10:33 AM
  5. Replies: 2
    Last Post: 05-27-2013, 09:35 AM
  6. [SOLVED] Vlookup formula - return only numbers for cells containing text and numbers
    By Andrew E Smith in forum Excel General
    Replies: 11
    Last Post: 07-03-2012, 06:07 AM
  7. formula for total of cells which contain numbers greater than zero
    By william39 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-14-2009, 06:36 AM

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