use of tilde in count

Question

Hi,

I used countif , so COUNTIF(RANGE, RANGE ) , but some of the characters are thing like question marks,

which I can put in quotation marks to make text, this works, but I thought if if put a tilde before them this should also work,

but if there is just 1, i get a 1, but if there are more than 1, I get zero's,

Why? Its really strange,

Richard.

Answer 1

Hi,

Share some data and show the expected result.

Answer 2

Hi Richard

It is impossible for us to help you if you don't provide us with clearer information about your scenario,

Please, share a picture of the data with the expected results.

What are those "things like question marks" you referring to?

Answer 3

Hi,

Well I've attached a list of characters and a screen shot of the problem with a bit more detail,

hope this helps.

The characters include anything listed here;

Usage	Behavior	Will match
?	Any one character	"A", "B", "c", "z", etc.
??	Any two characters	"AA", "AZ", "zz", etc.
???	Any three characters	"Jet", "AAA", "ccc", etc.
*	Any characters	"apple", "APPLE", "A100", etc.
*th	Ends in "th"	"bath", "fourth", etc.
c*	Starts with "c"	"Cat", "CAB", "cindy", "candy", etc.
?*	At least one character	"a", "b", "ab", "ABCD", etc.
???-??	5 characters with hypen	"ABC-99","100-ZT", etc.
*~?	Ends in question mark	"Hello?", "Anybody home?", etc.
*xyz*	Contains "xyz"	"code is XYZ", "100-XYZ", "XyZ90",

but if a tilde is placed in front what follows should be treated as text, so as you can see

I have done this to the two questions marks in the list ;

Now in the second column (ignore the first it's manually done as a check) is the formula

=COUNTIF(Table5[Column1],Table5[Column1])

But as you can see the two "?" are counted as zero, when I should have "2" where each occurs,

this is the case when speech marks are placed around them, they are counted correctly.

But the tide seem to turn them in to a blank, can you explain this.

I hope this is enough information.

Richard.

Answer 4

Hi,

In cell G14, this formula returns 3 as the answer (the number of ~? in the range)

=COUNTIF($E$5:$E$19,"~"&E14)

Hope this helps.