Forum Discussion
torircase
Mar 04, 2023Copper Contributor
Countif using specific criteria
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.
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":
Jones
The Jones
A Jones
*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.)