+ Reply to Thread
Results 1 to 26 of 26

Need formula for counting number of entries making up a sum equation in a cell

  1. #1
    Registered User
    Join Date
    01-11-2013
    Location
    Arizona
    MS-Off Ver
    2011
    Posts
    10

    Need formula for counting number of entries making up a sum equation in a cell

    I need a formula that will look in certain named cells and count how many entries are in those cells.

    For example, in one cell I might have this formula:

    =4+16+12+24

    Which results in the cell displaying the total "56".

    In another cell I have this formula:

    =5+20+7

    Which results in that cell displaying the total "32".

    In this case, such a formula would be directed to look at those two cells and count how many entries exist, returning the answer, in this case, "7". That is, 4 entries in the first cell plus 3 entries in the second cell = 7 entries.

    Does anyone know what the formula would be to do this?

  2. #2
    Registered User
    Join Date
    08-22-2012
    Location
    Zagreb, Croatia
    MS-Off Ver
    Excel 2003
    Posts
    61

    Re: Need formula for counting number of entries making up a sum equation in a cell

    Hi DHart,
    find attached file with following user Function defined. It's covering possibility of emty cell, one element cell (without formula shape) and formula shape cell. It's for add operator only. If you need to consider other than + operators, it's need to change IF statement in FOR structure.

    Please Login or Register  to view this content.
    Regards,
    Attached Files Attached Files
    If a post helps press star sign 4 my reputation

  3. #3
    Registered User
    Join Date
    01-11-2013
    Location
    Arizona
    MS-Off Ver
    2011
    Posts
    10

    Re: Need formula for counting number of entries making up a sum equation in a cell

    mnjofra.... thank you for that! I do need some help in implementing it, however. I put the =howmanyentries(CR) formula into my spreadsheet and all that I get as a result is #NAME?

    So, there's clearly something about how to use this that I don't understand. If anyone can chime in to tell me how to use the Function, I'd greatly appreciate it. Thank you.

  4. #4
    Registered User
    Join Date
    01-11-2013
    Location
    Arizona
    MS-Off Ver
    2011
    Posts
    10

    Re: Need formula for counting number of entries making up a sum equation in a cell

    What I did was paste my .xlxs spreadsheet data and format into your .xls spreadsheet with the macro already in it and the macro works on my data. YES!!! Thank you.

    When I tried to save it as an .xlsx spreadsheet, however, I got the message that Visual Basic macros are not supported in the .xlsx version format. Is there a way to convert the macro/function so it will work in an .xlsx spreadsheet?

  5. #5
    Registered User
    Join Date
    08-22-2012
    Location
    Zagreb, Croatia
    MS-Off Ver
    Excel 2003
    Posts
    61

    Re: Need formula for counting number of entries making up a sum equation in a cell

    DHart,
    if you use Office 2010 or Office 2007, type of your excel file should be xlsm. This "m" meaning that you have some macro code in the background. In case that you use format of 2003, xls is universal format for all type of excel files, with or without macroes. One additional thing, you have to set security level (2003) or trust controll settings (2007 or 2010) on mode to consider vba code.

    As a conclusion, you can use my old format file or change my file to xlsm new format. And enable vba execution mode.

    ... and mark item as solved and press a star for improve my reputation


    Regards,

  6. #6
    Registered User
    Join Date
    08-22-2012
    Location
    Zagreb, Croatia
    MS-Off Ver
    Excel 2003
    Posts
    61

    Re: Need formula for counting number of entries making up a sum equation in a cell

    DHart,
    if you use Office 2010 or Office 2007, type of your excel file should be xlsm. This "m" meaning that you have some macro code in the background. In case that you use format of 2003, xls is universal format for all type of excel files, with or without macroes. One additional thing, you have to set security level (2003) or trust controll settings (2007 or 2010) on mode to consider vba code.

    As a conclusion, you can use my old format file or change my file to xlsm new format. And enable vba execution mode.

    ... and mark item as solved and press a star for improve my reputation


    Regards,

  7. #7
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: Need formula for counting number of entries making up a sum equation in a cell

    Or maybe this UDF
    Please Login or Register  to view this content.
    This allows for both "+" and "-" signs in the source formula, and can sum a range of cells.

    See this workbook for usage examples.
    Attached Files Attached Files
    Last edited by Marcol; 01-14-2013 at 06:26 AM.
    If you need any more information, please feel free to ask.

    However,If this takes care of your needs, please select Thread Tools from menu above and set this topic to SOLVED. It helps everybody! ....

    Also
    اس کی مدد کرتا ہے اگر
    شکریہ کہنے کے لئے سٹار کلک کریں
    If you are satisfied by any members response to your problem please consider using the small Star icon bottom left of their post to show your appreciation.

  8. #8
    Registered User
    Join Date
    01-11-2013
    Location
    Arizona
    MS-Off Ver
    2011
    Posts
    10

    Re: Need formula for counting number of entries making up a sum equation in a cell

    *4

    THANK YOU! Great job with this. Works like a charm.

    Quote Originally Posted by mnjofra View Post
    DHart,
    if you use Office 2010 or Office 2007, type of your excel file should be xlsm. This "m" meaning that you have some macro code in the background. In case that you use format of 2003, xls is universal format for all type of excel files, with or without macroes. One additional thing, you have to set security level (2003) or trust controll settings (2007 or 2010) on mode to consider vba code.

    As a conclusion, you can use my old format file or change my file to xlsm new format. And enable vba execution mode.

    ... and mark item as solved and press a star for improve my reputation


    Regards,

  9. #9
    Registered User
    Join Date
    01-11-2013
    Location
    Arizona
    MS-Off Ver
    2011
    Posts
    10

    Re: Need formula for counting number of entries making up a sum equation in a cell

    Marcol... thank you for the CountFactors formulae.

    I opened the spreadsheet and found that the countfactors formulae cells are returning zeros. Is there something I need to do to activate? Is there a macro in there somewhere? I did save the file as an xlsm, but that didn't activate the countfactors calculation.

    Quote Originally Posted by Marcol View Post
    Or maybe this UDF
    Please Login or Register  to view this content.
    This allows for both "+" and "-" signs in the source formula, and can sum a range of cells.

    See this workbook for usage examples.

  10. #10
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: Need formula for counting number of entries making up a sum equation in a cell

    UDFs, User Defined Functions, are macros.
    When you open the .xls, allow macros, then save as .xlsm.

    Not sure why you are getting zeros, if calculation is set to zero you will get #VALUE! errors.
    If you haven't saved as macro-enabled, you should have had a warning and then, if you proceded you get #NAME! errors.
    Last edited by Marcol; 01-14-2013 at 01:39 PM.

  11. #11
    Registered User
    Join Date
    01-11-2013
    Location
    Arizona
    MS-Off Ver
    2011
    Posts
    10

    Re: Need formula for counting number of entries making up a sum equation in a cell

    Marcol... I did enable macros and saved as an .xlsm, however the calcs aren't happening?

  12. #12
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: Need formula for counting number of entries making up a sum equation in a cell

    Can you post the workbook as you have saved it?

  13. #13
    Registered User
    Join Date
    01-11-2013
    Location
    Arizona
    MS-Off Ver
    2011
    Posts
    10

    Re: Need formula for counting number of entries making up a sum equation in a cell

    Here it is.
    Attached Files Attached Files

  14. #14
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: Need formula for counting number of entries making up a sum equation in a cell

    Try this workbook,
    I've amended the UDF to
    Please Login or Register  to view this content.
    Attached Files Attached Files

  15. #15
    Registered User
    Join Date
    01-11-2013
    Location
    Arizona
    MS-Off Ver
    2011
    Posts
    10

    Re: Need formula for counting number of entries making up a sum equation in a cell

    Marcol.... for some reason, the calcs are returning zeros. I enabled macros when opening. Could there be something I'm doing wrong?

  16. #16
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: Need formula for counting number of entries making up a sum equation in a cell

    I am not getting zeros when I open either of the attachments in posts #7 or #14.

    I omly have 2003 available at the moment, I'll ask someone with a later version to see if they can find out where the problem lies, hold in there!

  17. #17
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Need formula for counting number of entries making up a sum equation in a cell

    (Excel 2007) When you Update the UDF, re-enter the formula into B2, drag it down, this worked for me anyways..

    Hope this helps
    A picture may be worth a thousand words, BUT, a sample Workbook is worth a thousand screenshots!
    -Add a File - click advanced (next to quick post), scroll to manage attachments, click, select add files, click select files, select file, click upload, when file shows up at bottom left, click done (bottom right), click submit
    -To mark thread Solved- go top of thread,click Thread Tools,click Mark as Solved
    If you received helpful response, please remember to hit the * of that post

  18. #18
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Need formula for counting number of entries making up a sum equation in a cell

    I don't get that code at all to be honest-Find returns a range not a number in vba. I'd change the code to
    Please Login or Register  to view this content.
    Josie

    if at first you don't succeed try doing it the way your wife told you to

  19. #19
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: Need formula for counting number of entries making up a sum equation in a cell

    @ JP
    As I am evaluating the cell value .Find() will either return the value, or an error.

    I agree it isn't a normal way of using .Find()

    See the difference between the two UDFs with the "Running Totals" column dragged down
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Maybe it isn't as clever as I thought, but it seems to work with 2003 without a problem.

    Thanks for coming to help.

  20. #20
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Need formula for counting number of entries making up a sum equation in a cell

    FYI, I opened the workbook in post #14 and it works fine. No indication why it would not calc for you.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  21. #21
    Registered User
    Join Date
    01-11-2013
    Location
    Arizona
    MS-Off Ver
    2011
    Posts
    10

    Re: Need formula for counting number of entries making up a sum equation in a cell

    I am far less knowledgeable than most of you regarding technical aspects of Excel.

    I'm running Excel for Mac 2011, OS 10.8.2, and when I opened the file in post #14 I was given the dialog box with option to enable macros, which I selected.

    If anyone can suggest things which I should try to make the calks work, that would be great. The calcs return zero at present. Thank you.

  22. #22
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Need formula for counting number of entries making up a sum equation in a cell

    Check my post(#17) you may have to re-enter for each column and drag down..excel seems to have problems with auto-updating UDF's, so if you change one, you need to re-enter it(apparently anyways)

  23. #23
    Registered User
    Join Date
    01-11-2013
    Location
    Arizona
    MS-Off Ver
    2011
    Posts
    10

    Re: Need formula for counting number of entries making up a sum equation in a cell

    OK... I re-entered (re-keyed in) the equation in B2, then copied it down to lower cells. The cells in column B still return the calc of "0". I'm baffled.

  24. #24
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Need formula for counting number of entries making up a sum equation in a cell

    Could you upload it (again, I know)? but unless this is a MAC thing, I really can not see why it's not working for you...

  25. #25
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Need formula for counting number of entries making up a sum equation in a cell

    I can see what it's doing but
    -I don't see why you do it that way
    -it doesn't work in 2011. I've a feeling it's the old Find in a udf issue that used to affect windows versions

    Instr should work in all versions I reckon

  26. #26
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: Need formula for counting number of entries making up a sum equation in a cell

    Okay, I'll go with JP, I've had problems with .Find() before.

    Try this UDF, or JPs'
    Please Login or Register  to view this content.
    Try this workbook
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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