Is countif the right function?

  • Thread starter Thread starter bill
  • Start date Start date
B

bill

I ha as ve a range of cells, say A1:D12,,and I have these set up as data
valadation, with a list such as the following.
Cat
Cat*
Cat**
Cat***
to choose from.
on the same sheet, I want know how many cells have the text "cat", how many
have the text "cat*" etc...I used the countIf and it seems that it can not
differentiate between whether cat has one* or two **. can someone assist me
with this formula..Thanks
Bill
 
Bill,

* is a wild card that matches any sequence of characters. If you want to specify a single *, use ~*. For example, to find all
occurences of cat** use

=COUNTIF(rng,"cat~*~*")
 
Hi Bill,
Asterisk is a wildcard if you want to check for an asterisk you will
have to precede it with a tilde
=COUNTIF(A1:A12,"Cat~*~*")

wildcards, for Find/Replace/Search Worksheet Functions in Excel precede
wildcard characters of ?, or *, or ~ by a ~ (tilde).
wildcards: ? single character, * any number of characters. Search is
case sensitive, Find and Replace are not case sensitive. Applies also
to shortcuts: Ctrl+F, Ctrl+H

Don't confuse with ampersand(&) formatting text in headers and footers (
to type a plain ampersand double them as in AT&&T.

I guess you know how you are going to use it in cell validation.
 
Thanks guys, it works great. Bill
David McRitchie said:
Hi Bill,
Asterisk is a wildcard if you want to check for an asterisk you will
have to precede it with a tilde
=COUNTIF(A1:A12,"Cat~*~*")

wildcards, for Find/Replace/Search Worksheet Functions in Excel precede
wildcard characters of ?, or *, or ~ by a ~ (tilde).
wildcards: ? single character, * any number of characters. Search is
case sensitive, Find and Replace are not case sensitive. Applies also
to shortcuts: Ctrl+F, Ctrl+H

Don't confuse with ampersand(&) formatting text in headers and footers (
to type a plain ampersand double them as in AT&&T.

I guess you know how you are going to use it in cell validation.
Click to expand...
 
Back
Top