Counting a character in a cell that contains a string

[maic15]

I have an excel spreadsheet where the structure cannot change. I have cells that contain data that I need to count.
For example,
Cell Tange: B1:b45
O,FG,CJP
O,FG,CJP
O,FG,CJP

I need to count "O" in each cell. I tried Countif(Left(B1:B45,"O"),1) and it did not work.
Thoughts?

 

[Oaktree]

Try

=SUMPRODUCT(--(LEFT(B1:B45,1)="O"))

 

[Aladin Akyurek]

I have an excel spreadsheet where the structure cannot change. I have cells that contain data that I need to count.
For example,
Cell Tange: B1:b45
O,FG,CJP
O,FG,CJP
O,FG,CJP

I need to count "O" in each cell. I tried Countif(Left(B1:B45,"O"),1) and it did not work.
Thoughts?

If

=COUNTIF(B1:B45,"*O*")

does not fit the bill, maybe because you might be needing something like:

=SUMPRODUCT(--ISNUMBER(SEARCH(",O,",","&B1:B45&",")))

 

[maic15]

I wrote the post wrong...The "O" does not always apear first in the cell. I need to have code if it appears in another part of the string.

 

[Oaktree]

Did you try Aladin's?

=SUMPRODUCT(--ISNUMBER(SEARCH(",O,",","&B1:B45&",")))

 

[maic15]

Yes..But it only returend 11 and the total is 12..The "O" can appear in mutiple places

 

[HalfAce]

I need to have code if it appears in another part of the string

If:
1. O will only appear once in any given cell, and
2. I understand what you're after...
maybe something like this will help:


<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> CountO()
<SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, c <SPAN style="color:#00007F">As</SPAN> Range, Rng <SPAN style="color:#00007F">As</SPAN> Range
<SPAN style="color:#00007F">Set</SPAN> Rng = Range("B1:B45")
i = 0
<SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> Rng
    <SPAN style="color:#00007F">If</SPAN> InStr(c, "O") <SPAN style="color:#00007F">Then</SPAN> i = i + 1
<SPAN style="color:#00007F">Next</SPAN> c
MsgBox i
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Hope it helps,

(Hi Aladin. Nothing to report yet... )
Dan

 

[Todd Bardoni]

=SUMPRODUCT((LEN(B1:B45)-LEN(SUBSTITUTE(UPPER(B1:B45),"O",""))))