+ Reply to Thread
Results 1 to 22 of 22

Excel add total of entries separated by a + sign in a formula?

  1. #1
    Registered User
    Join Date
    12-23-2016
    Location
    Italy
    MS-Off Ver
    2010
    Posts
    8

    Excel add total of entries separated by a + sign in a formula?

    I'd like cell E433 to total the amount of entries entered into a formula in cell F433 where the formula is: =91.72+38+292.45+38.4+81.23+43.52. Additionally, the whole E column will use this formula. Cells in F column will either have numerical entries, or will have simply have "N/A". So I'd need it to recognize that as well, and show 0 when that happens.

    Any assistance would be appreciated. Thank you.

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Excel add total of entries separated by a + sign in a formula?

    Hi,

    It's not clear to me exactly what you want to see. Please upload an example workbook and manually add the results you expect clearly identifying which cells are data and which are results.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Excel add total of entries separated by a + sign in a formula?

    Is this what you wanted?

    =IF(ISNUMBER(A1),LEN(FORMULATEXT(A1))-LEN(SUBSTITUTE(FORMULATEXT(A1),"+",""))+1,0)

    assumes data start in A1. I know they don't, but for the sake of simplicity...
    Attached Files Attached Files
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  4. #4
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Excel add total of entries separated by a + sign in a formula?

    FORMULATEXT function
    Applies To: Excel 2016 Excel 2013 Excel 2016 for Mac Excel for Mac 2011

  5. #5
    Registered User
    Join Date
    12-23-2016
    Location
    Italy
    MS-Off Ver
    2010
    Posts
    8
    Richard,

    Example is attached. I've looked everywhere and can't find exactly what I'm looking for.

    Look at F14; there are a ton of entries in the formula all sitar by a + sign. I'd like for E14 to automatically total the number of entries. And I'd like to see a 0 when "N/A" I'd entered.





    Quote Originally Posted by Richard Buttrey View Post
    Hi,

    It's not clear to me exactly what you want to see. Please upload an example workbook and manually add the results you expect clearly identifying which cells are data and which are results.
    Attached Files Attached Files

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Excel add total of entries separated by a + sign in a formula?

    Sandy 666. I have Excel 2010 and it's working fine here.

  7. #7
    Registered User
    Join Date
    12-23-2016
    Location
    Italy
    MS-Off Ver
    2010
    Posts
    8

    Re: Excel add total of entries separated by a + sign in a formula?

    Glenn Kennedy,

    I end up getting the "#NAME?". Most solutions I see end up giving me that...

  8. #8
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Excel add total of entries separated by a + sign in a formula?

    Your sheet with my formula, working perfectly in Excel 2010.
    Attached Files Attached Files

  9. #9
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Excel add total of entries separated by a + sign in a formula?

    @Glenn, you must be using 2013: it does not work with 2010: #NAME? error as per Op's reply

  10. #10
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Excel add total of entries separated by a + sign in a formula?

    Are you ACTUALLY using Excel 2010?

    I have applied it in your sheet...
    Attached Images Attached Images

  11. #11
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Excel add total of entries separated by a + sign in a formula?

    Glenn,

    I wonder why this:

    ver. 14.0.7173.5000
    Ex 2010 32-bit

    not recognize FORMULATEXT() ?

  12. #12
    Registered User
    Join Date
    12-23-2016
    Location
    Italy
    MS-Off Ver
    2010
    Posts
    8

    Re: Excel add total of entries separated by a + sign in a formula?

    @Glenn

    I am using Microsoft Excel 2010. I've looked at your examples on both my work computer, and my phone via the excel android app. both give me #NAME?...

  13. #13
    Registered User
    Join Date
    12-23-2016
    Location
    Italy
    MS-Off Ver
    2010
    Posts
    8

    Re: Excel add total of entries separated by a + sign in a formula?

    @Sandy

    I'm using Microsoft Office Professional Plus 2010
    version 14.0.7116.5000 (32-bit)

  14. #14
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Excel add total of entries separated by a + sign in a formula?

    choneal,
    I see what u have in your description

  15. #15
    Registered User
    Join Date
    12-23-2016
    Location
    Italy
    MS-Off Ver
    2010
    Posts
    8

    Re: Excel add total of entries separated by a + sign in a formula?

    Sorry. Super annoyed with this. I'm not Excel stupid nor super savvy, but this formula has evaded me. I think it may be our work computer causing an issue, but I got the same problem on my phone too.

  16. #16
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Excel add total of entries separated by a + sign in a formula?

    Enough to read FORMULATEXT()

  17. #17
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,891

    Re: Excel add total of entries separated by a + sign in a formula?

    Try this ...
    Please Login or Register  to view this content.
    Use:

    =Choneal(F6)
    Attached Files Attached Files

  18. #18
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Excel add total of entries separated by a + sign in a formula?

    Well, I am using Excel 2010, whatever you all say!!


    Here is an alternative that uses the old XL4 Get.cell function. See named range (cTRL F3) for set up of FormulaAsText

    named range (FormulaAsText) =GET.CELL(6,OFFSET(INDIRECT("RC",FALSE),0,1))

    Then use

    =IFERROR(1/(1/(LEN(E6)-LEN(SUBSTITUTE(E6,"+",""))))+1,"N/A")

    in D6 copied down and

    =formulaAsText

    in E6 copied down (hide this column)

    Open as macro enabled...
    Attached Files Attached Files

  19. #19
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Excel add total of entries separated by a + sign in a formula?

    @Glenn

    Do you have any add-ins installed that might have a FORMULATEXT function? Opening your workbook in 2016 still produces a #NAME? error until re-entering the formula in each cell because there is no hidden _xlfn name for the function.
    Last edited by xlnitwit; 12-23-2016 at 08:55 AM.
    Don
    Please remember to mark your thread 'Solved' when appropriate.

  20. #20
    Registered User
    Join Date
    12-23-2016
    Location
    Italy
    MS-Off Ver
    2010
    Posts
    8
    Glenn and Phuocam

    Both of those work when I open your examples Phuocam looks to be spot on what I want). But I don't know enough about FORMULATEXT function to get it to work in my sheet.

    And just finishing my lunch, I realized that maybe some of my other functions and conditional formatting could be interfering. Idk. Here's the original in all of its glory. See what you think.

    Notice the formula at the top, and the conditional color change based on column J.


    Quote Originally Posted by Phuocam View Post
    Try this ...
    Please Login or Register  to view this content.
    Use:

    =Choneal(F6)
    Attached Files Attached Files

  21. #21
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Excel add total of entries separated by a + sign in a formula?

    See attached: VBA code added.
    Attached Files Attached Files

  22. #22
    Registered User
    Join Date
    12-23-2016
    Location
    Italy
    MS-Off Ver
    2010
    Posts
    8

    Re: Excel add total of entries separated by a + sign in a formula?

    Phuocam,

    I did realize you put that in the VBA... I works perfectly. Thanks for the help.

    And thank you all for your help; have a good holiday!

+ 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. Split comma separated entries to new rows
    By dandi10 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 05-02-2020, 07:59 PM
  2. [SOLVED] Split comma separated entries to new row
    By trefix in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 02-26-2014, 04:56 AM
  3. Total entries per month formula
    By danfullwood in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-21-2013, 08:54 AM
  4. [SOLVED] Need Micros to splitte Data with a column that is separated by tilde sign
    By Oszie in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 07-31-2013, 06:25 AM
  5. [SOLVED] Formula for total entries in a column and for percentage of certain value
    By SharpTJ in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-09-2013, 09:02 PM
  6. Excel 2007 : Formula for displaying Total Overdue entries.
    By noahsampeer in forum Excel General
    Replies: 4
    Last Post: 12-16-2010, 01:05 PM
  7. Using array formula to total entries
    By RichardL in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-20-2008, 03:08 AM
  8. [SOLVED] Formula to add top 4 entries in a column and total them at the bot
    By New2excel in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-07-2006, 01:20 AM

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