Count iterations of a letter excluding other characters

[jl2509]

Hi all,

In column A, I have codes allocated as the letter A and B for up to 5 iterations per cell (but could be more I guess dependent on growth). These are separated by "/" but I dont want to count this

How do I count the number of iterations the letters are used in the column starting from Cell A10: A110

The count in Cell A3 should be the total number of "A" in Cell A4 the total Number of "B" and in Cell A5 the sum total of "A" and "B"

i.e. in Column A

Cell A3 Count for "A": 26
Cell A4 Count for "B": 27
Cell A5 sum of "A" and "B": 33

A
A/A
A/A/A
A/A/A/A
B
B/B
B/B/B
B/B/B/B
A/B
A/B/B
A/B/B/B
A/B/B/B/B
B/A
B/A/A
B/A/A/A
B/A/A/A/A
B/B/A/A/B

etc

Thanks in advance.

 

[nemmi69]

How about this.
If A1 contains A/A/A . Enter in B1 = LEN(A1) - LEN(SUBSTITUTE(A1, "A", ""))

 

[jl2509]

How about this.
If A1 contains A/A/A . Enter in B1 = LEN(A1) - LEN(SUBSTITUTE(A1, "A", ""))

Thanks for the response, but sorry, I don't see how that works?

I cant put data in column B as that already contains data

Also the range is A10:A100 for the combinations?

Sample data below

 

[nemmi69]

Sorry,

So in A3 enter ="A=" & SUMPRODUCT(LEN(A10:A26) - LEN(SUBSTITUTE(A10:A26, "A", "")))

​

 

[jl2509]

That's works great thanks so much
Is there an option to make this fool proof in case anyone uses a lowercase a or b

 

[nemmi69]

="A=" & SUMPRODUCT(LEN(PROPER(A1:A21)) - LEN(SUBSTITUTE(PROPER(A1:A21), "A", "")))

 

[RoryA]

You don't really need the first PROPER there as LEN won't be affected by case.

 

[jl2509]

Much appreciated folks,
Can you provide an example of not using the first Proper please?

 

[nemmi69]

I think RoryA meant this

="A=" & SUMPRODUCT(LEN(A1:A21) - LEN(SUBSTITUTE(PROPER(A1:A21), "A", "")))

 

[jl2509]

Thanks for the update. another issue has just come to light, in that if a user inputs data without formatting correctly i.e. "aa" or "AAA" without a "/" the formula counts incorrectly. Can this be updated to include lowercase and poor formatting please. Sorry for the lack of definition in the original requirement, but its only through testing that you realise these things.