# Countif Case sensitive Letters

#### coliervile

##### Well-known Member
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

### Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

#### Richard Schollar

##### MrExcel MVP
Hi Charlie

You can do this with Sumproduct:

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

or:

Code:
``=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:

Code:
``=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

##### Well-known Member
Thanks eliW and RichardSchollar I'll try them out and see which one works best.

Regards

Charlie

##### MrExcel MVP
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)

Replies
4
Views
53
Replies
6
Views
106
Replies
3
Views
65
Replies
15
Views
153
Replies
2
Views
76

1,109,000
Messages
5,526,186
Members
409,686
Latest member
Tori83