Microsoft Excel Countif when cells contain unique strings separated by commas

Question

I have a project I am working on where I would like to count the amount of times a state's abbreviation shows up in a range of cells.

I have gotten it to work with a certain code, however, it doesn't work if the state shows up more than three times (ex: TX, CA, CA, CA, CA). For California, it'll return 3 instead of the 4 that it should.

Here's my current code

=COUNTIF(Table1[State],","&I17)+COUNTIF(Table1[State],Stats!‌​I17&",")+COUNTIF(Tab‌​le1[State],Stats!I17‌​)

The first COUNTIF takes into account if it follows a comma, the next if it precedes, and the last if "CA"(I17) is by itself in a cell.

I attached an example image of what I am trying to do here: Excel State Objective

Answer 1

If your data is in cell A1:

=(LEN(A1)-LEN(SUBSTITUTE(A1," CA","")))/3

Note that this will only work if your string only contains state abbreviations: if it has "TX, CA, My cat is a jerk", it will count the "ca" from "cat" as a valid match. It also assumes that there's a space before "CA", but it doesn't rely on commas (which is probably why your current code isn't working).