Help with FIND - COUNTIF formula

[TheReaper]

Hello guys!

I need help with a formula, hope somebody could help me.

I've been trying to figure out how to make a formula that would look for a text string and count the number of occurrences within a range of cells, but the problem is that the target cells have multiple values...

Data looks like this:

A B C
1 Process | Ocurrences | Cases
2 ACR ?? RRF, CLR, SPA
3 BUR ?? CDP, COD
4 AUR ?? GBU, CTC
5 CTR ?? CAR, SRD, RRD, BUN, BUF, BUD
6 SGR ?? RRF, CLR, SPA
7 CAR ?? CAR, SRD, RRD, BUN, BUF, BUD

... ... ...
... ... ...

So, I need a formula (in B2) to look for "ACR" in the whole C column and count the number of occurrences... and so on.

I don't know how to use macros neither how to create them... so, is it possible to use a standard formula with this?

Thanks in advance!
Have a nice day!

 

[Ron Coderre]

Using your posted structure...

This formula will count the number of cells in Col_C that contain the value
in A2:

B2: =COUNTIF($C:$C,"*"&A2&"*")

Copy that formula down as far as you need.

Using your sample data, the formula returns: 2
in B7..(there a are 2 cells that contain CAR)

Does that help?

 

[TheReaper]

Thanks a lot Ron!

yeap, it definitely works...

Thanks again!

 

[xenou]

I came up with:

1.
This formula will count the occurrences of ACR in a cell:
=(LEN(A1)-LEN(SUBSTITUTE(A1,"ACR","")))/3

2.
Put that formula in a "helper column" - such as Column D or even column Z

3.
In B2 Sum() the "helper Column"


Note: your columns don't seem to make sense (you say Column C but the data appears in Column A in your sample data).

EDIT-I was intending to Count ACR if it was in the same cell more than once - sounds like Ron's idea is better.