+ Reply to Thread
Results 1 to 10 of 10

Count Number Of Single Non-Blank Cells Not Containing Characters

  1. #1
    Forum Contributor swordswinger710's Avatar
    Join Date
    02-20-2008
    Location
    Bright, Canada
    MS-Off Ver
    2010
    Posts
    834

    Question Count Number Of Single Non-Blank Cells Not Containing Characters

    This one is driving me a bit nuts - what would be the most efficient formula to count the number of 22 specified cells that meet 4 criteria? I am using Google Sheets.

    Cells: I6,K6,O6,Q6,U6,W6,AA6,AC6,AG6,AI6,AM6,AO6,AS6,AU6,AY6,BA6,BE6,BG6,BK6,BM6,BQ6,BS6

    Criteria:
    1. Is not blank
    2. Does not contain a -
    3. Does not contain a /
    4. Does not contain a *

    I've tried using the format below, but something's not right:

    =COUNTIFS(INDIRECT({I6,K6,O6,Q6,U6,W6,AA6,AC6,AG6,AI6,AM6,AO6,AS6,AU6,AY6,BA6,BE6,BG6,BK6,BM6,BQ6,BS6}),"<>*-*",INDIRECT({I6,K6,O6,Q6,U6,W6,AA6,AC6,AG6,AI6,AM6,AO6,AS6,AU6,AY6,BA6,BE6,BG6,BK6,BM6,BQ6,BS6}),"<>*/*",INDIRECT({I6,K6,O6,Q6,U6,W6,AA6,AC6,AG6,AI6,AM6,AO6,AS6,AU6,AY6,BA6,BE6,BG6,BK6,BM6,BQ6,BS6}),"<>*~**")

    Thanks for any help with this!
    Last edited by swordswinger710; 03-27-2019 at 05:27 PM.
    There is so much good in the worst of us,
    And so much bad in the best of us,
    That it hardly behooves any of us
    To talk about the rest of us.

  2. #2
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    6,929

    Re: Count Number Of Single Non-Blank Cells Not Containing Characters

    Hi,

    This works in Excel, though I'm afraid I can't be sure if it also works in Sheets:

    =SUMPRODUCT(0+ISNUMBER(MATCH(MOD(COLUMN(I6:BS6)-MIN(COLUMN(I6:BS6))+1,6),{1;3},0)),1-ISNUMBER(MATCH(I6:BS6,{"";"-";"/";"*"},0)))

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  3. #3
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    UK
    MS-Off Ver
    various
    Posts
    1,252

    Re: Count Number Of Single Non-Blank Cells Not Containing Characters

    @XORLX,

    Looking at OP original, e.g. *~**, I think the expectation is that the characters would/could be embedded rather than representing the cell value itself?

    I wonder if perhaps you could adapt to something like below?

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

    I've no doubt you could improve upon it ...

    and, as you note, this is based on XL rather than Sheets - so apologies to OP if not suitable.

  4. #4
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    6,929

    Re: Count Number Of Single Non-Blank Cells Not Containing Characters

    @XLent

    Good spot and good solution.

    Cheers

  5. #5
    Forum Contributor swordswinger710's Avatar
    Join Date
    02-20-2008
    Location
    Bright, Canada
    MS-Off Ver
    2010
    Posts
    834

    Re: Count Number Of Single Non-Blank Cells Not Containing Characters

    Yeah, XLent, that works flawlessly! Thank you both for your assistance. I'm curious about the use of SUBSTITUTE though - why don't I get the same results using this:

    =SUMPRODUCT(0+ISNUMBER(MATCH(MOD(COLUMN(I20:BS20)-MIN(COLUMN(I20:BS20))+1,6),{1;3},0)),(I20:BS20<>"")-ISNUMBER(SEARCH("/","-","~*")))
    Last edited by swordswinger710; 03-22-2019 at 11:13 AM.

  6. #6
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    UK
    MS-Off Ver
    various
    Posts
    1,252

    Re: Count Number Of Single Non-Blank Cells Not Containing Characters

    well, it would get complicated because, in essence, you'd be performing three searches against each cell

    SEARCH({"/";"-";"~"},I20:BS20)
    note: inline array split on row basis {;} rather than column given column based text range (I20:BS20)

    so, in effect, you'd be generating a 3x63 array of values

    the fact this is different to your initial array (based on MOD of column) is surmountable, however it's further complicated because you want to treat each 3x1 as a single Boolean test - i.e. if 1+ positive search for any given cell then treat as TRUE, the proliferation of {expected} SEARCH errors makes that a little hard(er) to handle too.

    the approach offered basically just negates the above by using multiple SUBSTITUTEs over multiple SEARCHes thereby ensuring you are doing a single SEARCH, and therefore generating a simple 1x63 output.

    edit: XORLX may be able to put it a little nicer but, in short, you'd make your life a whole lot more complicated for no real efficiency gain
    Last edited by XLent; 03-22-2019 at 12:14 PM. Reason: typo {3x1 rather than 1x3!}

  7. #7
    Forum Contributor swordswinger710's Avatar
    Join Date
    02-20-2008
    Location
    Bright, Canada
    MS-Off Ver
    2010
    Posts
    834

    Re: Count Number Of Single Non-Blank Cells Not Containing Characters

    Very nice, thank you for that explanation.

    Another question I just came across - what if I need to do this with the same cells, but now instead of just counting the first cell in each column, I actually check 38 cells in each of the columns?

    Like this, but this doesn't work: =SUMPRODUCT(0+ISNUMBER(MATCH(MOD(COLUMN(I6:BS44)-MIN(COLUMN(I6:BS44))+1,6),{1;3},0)),(I6:BS44<>"")-ISNUMBER(SEARCH("/",SUBSTITUTE(SUBSTITUTE(I6:BS44,"*","/"),"-","/"))))

  8. #8
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    UK
    MS-Off Ver
    various
    Posts
    1,252

    Re: Count Number Of Single Non-Blank Cells Not Containing Characters

    FWIW, re: your penultimate post, one way you could do it w.SEARCH would be to split the tests into an OR (via addition), e.g.:

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

    re: latest question... you need to multiply the arrays given different dimensions
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Last edited by XLent; 03-22-2019 at 12:22 PM.

  9. #9
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    6,929

    Re: Count Number Of Single Non-Blank Cells Not Containing Characters

    XLent's analysis re dimensionality in post #6 is correct. One option to convert the resulting 3-row-by-63-column array would be to use MMULT, viz:

    =SUMPRODUCT(0+ISNUMBER(MATCH(MOD(COLUMN(I6:BS6)-MIN(COLUMN(I6:BS6))+1,6),{1;3},0)),0+(I6:BS6<>""),0+(MMULT({1,1,1},0+ISNUMBER(FIND({"/";"-";"*"},I6:BS6)))=0))

    Note that we can also use FIND in place of SEARCH; since the former does not allow wildcards, we negate the need to precede the asterisk with a tilde.

    Regards

  10. #10
    Forum Contributor swordswinger710's Avatar
    Join Date
    02-20-2008
    Location
    Bright, Canada
    MS-Off Ver
    2010
    Posts
    834

    Re: Count Number Of Single Non-Blank Cells Not Containing Characters

    Very helpful and useful information, thank you guys so much.

+ 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