+ Reply to Thread
Results 1 to 7 of 7

Formula for sum of cells which contain letters and numbers

  1. 07-09-2014, 04:39 AM #1
    Hussain Anees
    Hussain Anees is offline
    Registered User
    Join Date
    06-17-2014
    Location
    Male'
    MS-Off Ver
    Office 2010
    Posts
    31

    Formula for sum of cells which contain letters and numbers

    Hi,

    i have excel sheet which contains letters and numbers in the same cell.
    I want to sum the numbers in all the cells.

    Please tell me a formula for this calculation.

    attached is a sample.

    Thanks
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 07-09-2014, 04:45 AM #2
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,377

    Re: Formula for sum of cells which contain letters and numbers

    One way:

    Formula: copy to clipboard 
    Please Login or Register  to view this content.



    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke

    Reply With QuoteRegister To Reply
  3. 07-09-2014, 04:50 AM #3
    Punnam
    Punnam is offline
    Forum Expert
    Join Date
    03-28-2014
    Location
    Hyderabad,India
    MS-Off Ver
    Excel 2013
    Posts
    1,887

    Re: Formula for sum of cells which contain letters and numbers

    HI TMS Guru,

    Kindly guide me to understand the sum-product array .

    Punnam
    Reply With QuoteRegister To Reply
  4. 07-09-2014, 04:50 AM #4
    jewelsharma
    jewelsharma is offline
    Valued Forum Contributor
    Join Date
    12-02-2012
    Location
    Melbourne, VIC
    MS-Off Ver
    Excel 2016
    Posts
    750

    Re: Formula for sum of cells which contain letters and numbers

    if your string is always going to be "Mrf" (or basically any string exactly 3 character long), the you can use the following array formula:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.

    Note: An array formula must be entered as Ctrl + Shift + Enter.
    Reply With QuoteRegister To Reply
  5. 07-09-2014, 05:16 AM #5
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,377

    Re: Formula for sum of cells which contain letters and numbers

    See the updated example for an explanation.


    If you are satisfied with the solution(s) provided, please mark your thread as Solved.


    New quick method:
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

    Or you can use this way:

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save


    You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  6. 07-09-2014, 05:30 AM #6
    Punnam
    Punnam is offline
    Forum Expert
    Join Date
    03-28-2014
    Location
    Hyderabad,India
    MS-Off Ver
    Excel 2013
    Posts
    1,887

    Re: Formula for sum of cells which contain letters and numbers

    Hi Tms,

    Thanks for the explanation .

    Punnam
    Reply With QuoteRegister To Reply
  7. 07-09-2014, 05:33 AM #7
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,377

    Re: Formula for sum of cells which contain letters and numbers

    You're welcome. Thanks for the rep.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Sum cells containing numbers and letters
    By Texas10 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 07-09-2014, 07:59 PM
  2. [SOLVED] Separating a column by numbers vs. letters when numbers are at the end of the cells?
    By Skywalker in forum Excel General
    Replies: 4
    Last Post: 02-04-2012, 01:46 PM
  3. Sum cells that have numbers and letters in them!
    By anthgav in forum Excel General
    Replies: 4
    Last Post: 01-11-2010, 12:40 PM
  4. How to add cells with letters and numbers
    By spud in forum Excel General
    Replies: 2
    Last Post: 06-10-2008, 05:21 PM
  5. [SOLVED] Sorting - cells containing numbers, numbers and letters
    By Gunny in forum Excel General
    Replies: 5
    Last Post: 07-15-2006, 08:30 PM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1