+ Reply to Thread
Results 1 to 13 of 13

Count non-blank, non-numeric, non-zero, non-space, non-error text cells (2007)

  1. 01-29-2010, 02:20 PM #1
    JayUSA
    JayUSA is offline
    Forum Contributor
    Join Date
    04-26-2009
    Location
    Austin, TX
    MS-Off Ver
    Excel 2016
    Posts
    241

    Count non-blank, non-numeric, non-zero, non-space, non-error text cells (2007)

    Hi Everyone,

    I know different variations of this question get asked all the time... but I can't find an answer that I'm sure will *always* do what I want.

    I have a range of cells (A1:A10), and I want to count all the text entries of positive length. That is to say, I don't want to count:

    blanks
    numbers
    zeros
    spaces
    errors

    I'm sure I need to use the LEN function, but I can't quite figure out how.

    Cheers!

    Jay


    FYI: this counting expression will be inserted in a SUMPRODUCT formula
    Last edited by JayUSA; 01-29-2010 at 04:32 PM.
    Reply With QuoteRegister To Reply
  2. 01-29-2010, 02:27 PM #2
    JBeaucaire
    JBeaucaire is offline
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Count non-blank, non-numeric, non-zero, non-space, non-error text cells (2007)

    Hey Jay.

    Please upload a sample workbook with complete examples of the data in column A and manually mockup the expected results in another section.

    Perhaps LEN() will work, perhaps not. Spaces are text, so you're already on trouble there, which is why it will be clearest if you demonstrate fully in a workbook what you're doing and expecting.

    Make sure the sample isn't oversimplified.
    _________________
    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!)
    Reply With QuoteRegister To Reply
  3. 01-29-2010, 02:39 PM #3
    NBVC
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Count non-blank, non-numeric, non-zero, non-space, non-error text cells (2007)

    Assuming list is in J2:J10,

    =SUMPRODUCT(--(ISTEXT(J2:J10)),--ISERR(SEARCH(" ",J2:J10)))

    if you are inserting into another sumproduct, insert --(ISTEXT(J2:J10)),--ISERR(SEARCH(" ",J2:J10))
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
    Reply With QuoteRegister To Reply
  4. 01-29-2010, 03:35 PM #4
    JayUSA
    JayUSA is offline
    Forum Contributor
    Join Date
    04-26-2009
    Location
    Austin, TX
    MS-Off Ver
    Excel 2016
    Posts
    241

    Re: Count non-blank, non-numeric, non-zero, non-space, non-error text cells (2007)

    Hi NBVC and JBeaucaire,

    I tried NBVC's suggested formula and it works well, except that it seems not to count valid text entries that include a space; for example: "there are several words in this valid text entry".

    I took JBeaucaire's suggestion, and implemented NBVC's formula with some sample data. That sample sheet is attached if you care to look.

    Once again. many, many thanks to both of you for your assistance in all matters!

    Cheers!

    Jay
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  5. 01-29-2010, 03:46 PM #5
    shg
    shg is offline
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Count non-blank, non-numeric, non-zero, non-space, non-error text cells (2007)

    Maybe =SUMPRODUCT( ISTEXT(A1:A10) * (IF(ISERROR(A1:A10), 0, LEN(TRIM(A1:A10) )>0) ) ), array-entered.
    Entia non sunt multiplicanda sine necessitate
    Reply With QuoteRegister To Reply
  6. 01-29-2010, 03:50 PM #6
    JayUSA
    JayUSA is offline
    Forum Contributor
    Join Date
    04-26-2009
    Location
    Austin, TX
    MS-Off Ver
    Excel 2016
    Posts
    241

    Re: Count non-blank, non-numeric, non-zero, non-space, non-error text cells (2007)

    shg,

    That's the final missing bit! It now works!

    Thanks to everyone who pushed the ball along!

    Cheers,

    Jay
    Reply With QuoteRegister To Reply
  7. 01-29-2010, 04:09 PM #7
    NBVC
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Count non-blank, non-numeric, non-zero, non-space, non-error text cells (2007)

    Quote Originally Posted by JayUSA View Post

    I tried NBVC's suggested formula and it works well, except that it seems not to count valid text entries that include a space; for example: "there are several words in this valid text entry".


    Once again. many, many thanks to both of you for your assistance in all matters!

    Cheers!

    Jay
    Yes, my formula assumed you had one word entries always.. and that the space would have been someone hitting a spacebar instead of leaving a cell blank.. The SEARCH() part looks for spaces in all cells and discounts them..
    Reply With QuoteRegister To Reply
  8. 01-29-2010, 04:48 PM #8
    JayUSA
    JayUSA is offline
    Forum Contributor
    Join Date
    04-26-2009
    Location
    Austin, TX
    MS-Off Ver
    Excel 2016
    Posts
    241

    Re: Count non-blank, non-numeric, non-zero, non-space, non-error text cells (2007)

    Hi Everyone,

    I actually have a follow-up question.

    I would like to apply shg's formula within an Offset function.

    So, I inserted the following as the "column offset" (term 3) of my OFFSET formula:


    Please Login or Register  to view this content.
    ... the problem is that the expression -- while doing exactly what I originally asked -- always generated a "column offset" value that is 1 too high!

    So, I added -1 to the end of my "column offset" expression, like this:

    Please Login or Register  to view this content.
    But that just generates a bunch of negative offset values -- not at all what I'm trying to do.

    I just want the offset values - calculated by shg's formula -- to be 1 less than are currently produced by the expression.

    Is that possible?

    Cheers,

    Jay
    Reply With QuoteRegister To Reply
  9. 01-29-2010, 04:55 PM #9
    NBVC
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Count non-blank, non-numeric, non-zero, non-space, non-error text cells (2007)

    Are you try to get to a position or are you trying to get a size of range...

    I.e. maybe that should be the 4th argurment ([Height])?

    It would be better if we say the whole formula and you explain exactly what you want... a sample sheet wouldn't hurt
    Reply With QuoteRegister To Reply
  10. 01-29-2010, 06:04 PM #10
    JayUSA
    JayUSA is offline
    Forum Contributor
    Join Date
    04-26-2009
    Location
    Austin, TX
    MS-Off Ver
    Excel 2016
    Posts
    241

    Re: Count non-blank, non-numeric, non-zero, non-space, non-error text cells (2007)

    Hi NBVC and shg and others!

    I've done just as you asked and am attaching a simple worksheet that clearly shows how I hope to use the counting formula(s) discused in this thread as a "column offset"' value.

    I look forward to your comments!

    Cheers!

    Jay
    Attached Files Attached Files
    Last edited by JayUSA; 01-29-2010 at 07:18 PM.
    Reply With QuoteRegister To Reply
  11. 01-29-2010, 08:11 PM #11
    JayUSA
    JayUSA is offline
    Forum Contributor
    Join Date
    04-26-2009
    Location
    Austin, TX
    MS-Off Ver
    Excel 2016
    Posts
    241

    Re: Count non-blank, non-numeric, non-zero, non-space, non-error text cells (2007)

    Hi Everyone,

    Well, I've made a big step forward....

    I have an equation that counts non-blank, non-space, non-zero cells in the range $L$6:$L14,and then subtracts 1, to determine a "column offset" value.

    Here is the whole formula:

    Please Login or Register  to view this content.
    And I've updated the attached spreadsheet with this code.

    So... the only remaining problems are:

    a) numeric values other than zero

    b) error values

    I don't want to count either of the above, if they appear in the range.

    I look forward to your observations!

    Cheers!

    Jay
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  12. 09-17-2012, 12:50 PM #12
    Robert_Brownsons
    Robert_Brownsons is offline
    Registered User
    Join Date
    09-17-2012
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: Count non-blank, non-numeric, non-zero, non-space, non-error text cells (2007)

    Hello Jay, How do you do? I know its been 2 years but have you solved the problem? Thank you
    Reply With QuoteRegister To Reply
  13. 09-17-2012, 01:03 PM #13
    arlu1201
    arlu1201 is offline
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Count non-blank, non-numeric, non-zero, non-space, non-error text cells (2007)

    Robert,

    Welcome to the Forum, unfortunately:

    Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]
    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)

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