# Countifs function using special characters

#### msenthil

##### New Member
Hi how to differentiate between "cat" and "cat*" using countif function

### Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

#### bbott

##### Well-known Member
Could you be more specific about the problem that you're trying to solve?

#### msenthil

##### New Member
Could you be more specific about the problem that you're trying to solve?

I have the following table
Cat
Cat
Cat*
Cat*
Cat*

When I use countif to count the the occurences of "cat" & "cat*" , I get the following results
Cat-2
Cat*-5
Basically the function is not able to differentiate between the strings "Cat" and "Cat*"

#### Trouttrap2

##### Well-known Member
Excel interprets the "*" as a wild card character. So the cat* means, give me any text string that starts with "cat". Use:
=COUNTIF(\$A\$1:\$A\$5,"cat~*")
=COUNTIF(\$A\$1:\$A\$5,"cat")

#### msenthil

##### New Member
Excel interprets the "*" as a wild card character. So the cat* means, give me any text string that starts with "cat". Use:
=COUNTIF(\$A\$1:\$A\$5,"cat~*")
=COUNTIF(\$A\$1:\$A\$5,"cat")
Thank you!
But I was trying to refrence the cell containg the String "Cat*". COuld you help me with that?

#### Trouttrap2

##### Well-known Member
you can try this
=COUNTIF(A1:A5,SUBSTITUTE(C2,"*","~*"))

C2 holds your text string "cat*"

Replies
2
Views
43
Replies
3
Views
119
Replies
0
Views
153
Replies
19
Views
278
Replies
3
Views
118

1,195,936
Messages
6,012,394
Members
441,695
Latest member
MickRobertson

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back