+ Reply to Thread
Results 1 to 6 of 6

summing a partial cell with a specified text in the same row

  1. #1
    Forum Contributor
    Join Date
    12-21-2011
    Location
    MS, USA
    MS-Off Ver
    Excel 2013
    Posts
    119

    summing a partial cell with a specified text in the same row

    Good morning everyone. Is it possible to sum a cell that contains alphanumeric? What I was trying to do is, if a cell contains the letter "S" for sick leave with a number of hours, for instance S-3, on one of the cell, and another cell S-2 in that same row range, then sum it on a specific cell as 5.

    I plan on doing this for different types of leave such as V for vacation, C for credit hours, etc.. but with the same concept.

    But numeric is the counts of hours, so if there for instance. S-2.5 hour, then I want the sum to consider the sum to the hour. say for example. S-2.5 in cell one, S-4.75 in cell two, then the sum should be 7.25..
    Last edited by preciouslife73; 12-22-2017 at 03:35 PM.

  2. #2
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.56 for Mac (home)
    Posts
    7,880

    Re: summing a partial cell with a specified text in the same row

    yes it is, see this link and see if that will help you...
    https://www.excelforum.com/excel-gen...fter-text.html
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  3. #3
    Forum Contributor
    Join Date
    12-21-2011
    Location
    MS, USA
    MS-Off Ver
    Excel 2013
    Posts
    119

    Re: summing a partial cell with a specified text in the same row

    Thank you. I will look into it when I get home.

  4. #4
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.56 for Mac (home)
    Posts
    7,880

    Re: summing a partial cell with a specified text in the same row

    good, check posts 7 and 11 for formulas that work, you can adjust them as necessary.

  5. #5
    Forum Contributor
    Join Date
    12-21-2011
    Location
    MS, USA
    MS-Off Ver
    Excel 2013
    Posts
    119

    Re: summing a partial cell with a specified text in the same row

    Hey Sambo, it does work! However, Why do we have to double click the cell, then do the Control+Shift+Enter? how do I override this to be automatic. Maybe by pressing a macro button?

    Oh I got it. Never mind. I see now it is automatic. so whenever it is created, you have to activate it. Got it!

    One more question lets say there is another I want to add two different codes together "CRU" and "CRE", how do I replace them in the formula?

    Thanks!
    Last edited by preciouslife73; 12-24-2017 at 03:41 PM.

  6. #6
    Forum Contributor
    Join Date
    12-21-2011
    Location
    MS, USA
    MS-Off Ver
    Excel 2013
    Posts
    119

    Re: summing a partial cell with a specified text in the same row

    I got it! I figured it out....
    PHP Code: 
    =SUM(IFERROR(--SUBSTITUTE(SUBSTITUTE(B$2:B$32,"CRE:",""),"CRU:",""),"")) 

+ 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. Replies: 2
    Last Post: 03-01-2016, 08:01 PM
  2. [SOLVED] Help to extract partial text from a cell
    By doublew in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 12-12-2014, 02:00 PM
  3. [SOLVED] remove partial text from cell
    By D Casten in forum Excel General
    Replies: 6
    Last Post: 07-13-2014, 11:11 PM
  4. [SOLVED] Match partial text when partial text is not exact
    By NamiSama in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 05-21-2014, 07:18 PM
  5. need help sorting by partial text in a cell - I think! :)
    By SmithAngels in forum Excel General
    Replies: 16
    Last Post: 10-11-2013, 09:58 AM
  6. matching partial text within one cell to partial text within another
    By Solstice in forum Excel - New Users/Basics
    Replies: 4
    Last Post: 03-03-2010, 09:13 PM
  7. Summing partial strings.
    By chris.a.mitchell@btinternet.com in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-08-2005, 07:05 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