Thread: [Solved] Countif problem

I collect logoed golf balls and use Excel as a database to keep track of them. I have nearly 9000 in my collection so far. I use the COUNTIF function to give me a running total as they are not all on the same worksheet. I have made a summary worksheet to keep track of how many I have from each state. Here is an example of an entry...
Tierra Santa GC [Weslaco,Texas] blue c,/org quadrant badge,2cl
Using COUNTIF(range,"*Texas*") gives me a running total of how many balls from Texas. To be accurate I would need to get only the result from what is inside the braket and not the entire text string. An example would be Washington, there is a Washington in Michigan that would make the result less accurate.

I sure would appreciate any help...

Try including the brackets like this

=COUNTIF(Range,"*[*Texas*]*")

or if Texas is in cell A1 you can reference that like this

=COUNTIF(Range,"*[*"&A1&"*]*")

You might still get discrepancies where one State is a "subset" of another, e.g. Kansas and Arkansas, if there is always a comma before the state you could include that in the COUNTIF too, in order to avoid miscounting.......