Countif Case sensitive Letters

[coliervile]

Can the COUTNIF function count small letters and Capital letters. For example A1:A50 contains the letters (random) L, l, T, n, W, a, A, F, E, a, e..... and so on what formula would be used to count all of the Small -a-

=Countif(A1:A50,"a") would count all of the a's

Regards

Charlie

 

[eliW]

Hi Charlie,

It can be done by adding another column and using Exact formula.

See below.

Eli

Book1
	A	B	C	D
1	a	TRUE	5
2	A	FALSE
3	a	TRUE
4	A	FALSE
5	a	TRUE
6	A	FALSE
7	a	TRUE
8	A	FALSE
9	a	TRUE
10	A	FALSE
11	A	FALSE
12	A	FALSE
13	A	FALSE
14	A	FALSE
15
data

 

[Richard Schollar]

Hi Charlie

You can do this with Sumproduct:

=SUMPRODUCT(LEN(A1:A50)-LEN(SUBSTITUTE(A1:A50,"A","")))

or:

=SUMPRODUCT(LEN(A1:A50)-LEN(SUBSTITUTE(A1:A50,B1,"")))

Where B1 holds the character you are counting (such as "a","b","B" etc).

EDIT: I initially thought you might have more than individual letters in each of your cells (eg "HGAavbga" in a single cell) and my formula will work for single occurrences too, altho it possibly isn't the most efficient. if there are only single letters in your cells then maybe:

=SUMPRODUCT(ISNUMBER(FIND("A",A1:A50))+0)

again you can replace the "A" with a cell reference holding the value (like B1 if B1 held "A" w/o quotes).

 

[coliervile]

Thanks eliW and RichardSchollar I'll try them out and see which one works best.

Regards

Charlie

 

[Aladin Akyurek]

Can the COUTNIF function count small letters and Capital letters. For example A1:A50 contains the letters (random) L, l, T, n, W, a, A, F, E, a, e..... and so on what formula would be used to count all of the Small -a-

=Countif(A1:A50,"a") would count all of the a's

Regards

Charlie

If A1:A50 consists of single char entries, that is, T, not cT...

=SUMPRODUCT(EXACT(A1:A50,"A")+0)