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

1. ## 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!

2. ## 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

3. ## 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:
`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. ## Re: Count Number Of Single Non-Blank Cells Not Containing Characters

@XLent

Good spot and good solution.

Cheers

5. ## 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("/","-","~*")))

6. ## 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

7. ## 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. ## 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:
`Please Login or Register  to view this content.`

re: latest question... you need to multiply the arrays given different dimensions
Formula:
`Please Login or Register  to view this content.`

9. ## 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. ## Re: Count Number Of Single Non-Blank Cells Not Containing Characters

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

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

#### 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