count if in excel - can this be done

Hi,

can this be done using countif
you have three cities, Toronto (TOR), Calgary (CAL) and Vancouver (VAN) and you have ID tags for sites in those locations.
column 'A' will have the cities, column 'B' will have the formula and column 'C' will have the list of sites.
example
A
Calgary

B
2

C
(DON-CAL 10293 B)
(DON-CAL 104759 C-5)

and so forth

please let me know if you would need a copy of raw data

If each location will be enclosed in parentheses, this regular formula returns the count of right-parentheses in Col_C
B2: =LEN(C2)-LEN(SUBSTITUTE(C2,")",""))

Is that something you can work with?

I am not sure how to apply that to the spreadsheet. I have never used LEN before.

Let me attach a sample file

Attachment 155692

I misunderstood your example...I thought that one cell could contain more than one location.
To do what you want, you'd need some way to define the city abbreviation so a formula like this would work:
B5: =COUNTIF($C$1:$C$40,"*-VAN-*")

That regular formula returns the count of Col_C items that contain -VAN-
In your posted workbook, the count is: 12

Is that something you can work with?

yes, i adjusted it more

=COUNTIF(E:E,"*CAL*")

and it provides the results I needed. I was close when trying to figure it out i had the "*" on the outside of the quotes and not the inside

You might need to leave the dashes on either side of the city abbreviation to avoid counting cells that coincidently have that text.
Example:
(DON-MTL-AdvantageTours-Cab3) contains "van" but doesn't refer to Vancouver.

Originally Posted by Ron Coderre

You might need to leave the dashes on either side of the city abbreviation to avoid counting cells that coincidently have that text.
Example:
(DON-MTL-AdvantageTours-Cab3) contains "van" but doesn't refer to Vancouver.

you logic is sound, ty again!