Countif using specific criteria

Occasional Visitor

I am using the CountIF formula to count how many times a name appears across several sheets (all within the same workbook). But I need to separate each name into two different categories. So, every time a name falls under Category 1, the name is typed regularly and CountIF adds it to the total.


If the name falls under category 2, I differentiate it by adding a * in front of their name. This makes sure that names with a * in front of it, are not added into the category 1 total.


Now though, if I use CountIF to try to total the names with a * in front of it, it just totals ALL of the times that their name appears even if it doesn't have an * . Is there a smarter way to separate the two categories?


For reference-- this is on an excel calendar I created so sometimes a name falls under a category 1 and a category 2 within the same sheet, so making a separate sheet and totaling it that way is not an option.

1 Reply


Excel treats * as a wildcard; it stands for 'any number of characters (including 0)'.

If you use "*Jones" as criteria in a COUNTIF formula, Excel includes all cells whose value ends in "Jones":


The Jones

A Jones



So * is not suitable to distinguish the two categories. Use another character, e.g. | or !

(Avoid ? since it is a wildcard character too, and =, + and - as well since they start a formula.)