Page 1 of 2 1 2 LastLast
Results 1 to 10 of 16

Thread: Sum text and numbers

  1. #1

    Sum text and numbers



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi,

    I am trying to sum a set of row that contains Numbers and Texts. Can you help please.

    Example:
    A B C D E F
    M5 M2 M6 P2 P1 B5

    I just want colum A7 to A1:F1 sum M5+M2+M6+P2+P1+B5 = 21

    I am unable to sum with the letter infront of the number.

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,477
    Articles
    0
    Excel Version
    Excel 2016
    Assuming it is only 1 letter in front, try:

    =SUMPRODUCT(--MID(A1:F1,2,10))


  3. #3
    Quote Originally Posted by NBVC View Post
    Assuming it is only 1 letter in front, try:

    =SUMPRODUCT(--MID(A1:F1,2,10))
    Hi Thanks
    But I'm still getting #VALUE!

  4. #4
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,477
    Articles
    0
    Excel Version
    Excel 2016
    See attached:

    Is there anything different than yours?
    Attached Files Attached Files


  5. #5
    Hi sorry I forget some of the cell in the same row are empty... probably that is why I'm getting the error message.
    thanks a lot for your help. What should i do?
    Attached Files Attached Files
    Last edited by Ash-uk; 2013-06-07 at 08:27 PM. Reason: Adding additional info

  6. #6
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,477
    Articles
    0
    Excel Version
    Excel 2016
    Ok then try:

    =SUMPRODUCT(--(0&MID(A1:H1,2,10)))

    where A1:H1 is the full range.


  7. #7
    You must think I'm Dumb ... I did try but given me zero.
    I attached the workbook

  8. #8
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,477
    Articles
    0
    Excel Version
    Excel 2016
    Your sample is not attached....

    Here is mine with updated formula....
    Attached Files Attached Files


  9. #9
    Hi I manage to used the formula but would not add the cell with numbers only !!

  10. #10
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,477
    Articles
    0
    Excel Version
    Excel 2016
    You keep changing the sampleset.....

    Try:

    =SUMPRODUCT(--(0&MID(A1:H1,2,10)))+SUM(A1:H1)

    with same assumption of A1:H1 being your dataset.

    that's also assuming single digits cells....

    If multi-digit is possible... then:

    =SUMPRODUCT(--ISERR(A1:H1+0),--(0&MID(A1:H1,2,10)))+SUM(A1:H1)
    Last edited by NBVC; 2013-06-07 at 08:53 PM.


Page 1 of 2 1 2 LastLast

Posting Permissions

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