Countif Case sensitive Letters

coliervile

Well-known Member
Joined
May 19, 2006
Messages
724
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
 

Some videos you may like

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
Joined
Apr 19, 2005
Messages
23,707
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
Joined
May 19, 2006
Messages
724
Thanks eliW and RichardSchollar I'll try them out and see which one works best.

Regards

Charlie
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,192
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)
 

Watch MrExcel Video

Forum statistics

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

This Week's Hot Topics

Top