Help Counting Alpha Characters

  • Thread starter Thread starter neon
  • Start date Start date
N

neon

I have an array B2:H28. These cells can contain any combination of
characters A,B,C,D,E,F,G,H.
I want to count the number of As and Bs etc.
I am using =COUNTIF(B2:H28,"A") and =COUNTIF(B2:H28,"B") ... which
only works if the cell contains single alpha characters. Once there is
a combination it falls apart.
Any help appreciated
 
Try COUNTIF(B2:H28,"A*")
Works for me but if cell has ABCA, only one A is counted.
 
Neon,

I think this will do it for you

=SUMPRODUCT(LEN(B2:H28))-SUMPRODUCT(LEN(SUBSTITUTE(B2:H28,"A","")))

Counts the As

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
That can be shortened a bit


=SUMPRODUCT(LEN(B2:H28)-LEN(SUBSTITUTE(B2:H28,"A","")))

or

=SUM(LEN(B2:H28)-LEN(SUBSTITUTE(B2:H28,"A","")))

array entered
 
Back
Top