Counting characters

[m1ner]

Hi,I'm trying to count number of characters across few cells and I can't do it.I have entered :
=SUMPRODUCT(LEN(B3:B14)-LEN(SUBSTITUTE(B3:B14,"N","L","E"))) to check number of N,L,E because I will need them later to create IF formula.Please help.
I forget to add that above formula did not work

 

[VBA Geek]

=sumproduct(len(b3:b14)-len(substitute(upper(b3:b14),"N", ""))+len(b3:b14)-len(substitute(upper(b3:b14),"L", ""))+len(b3:b14)-len(substitute(upper(b3:b14),"E", "")))

 

[m1ner]

Thank you very much VBA Geek.That worked but how to do counting for each letter separately? At the moment result is 12 but I would need results for every letter.

 

[VBA Geek]

For every letter then you may wanna break down the above formula in 3 parts

for instance =SUMPRODUCT(LEN(B3:B14)-LEN(SUBSTITUTE(UPPER(B3:B14),"N",""))) will count you the letters "N"

 

[Rick Rothstein]

Thank you very much VBA Geek.That worked but how to do counting for each letter separately? At the moment result is 12 but I would need results for every letter.

Your question just became unclear given what you wrote above. Is your ultimate goal to find out how many alphabetic letter characters, in total, you have in a single column range... that is, a single number result for all the letters... or did you need 26 individual counts, one for each letter individually?

 

[m1ner]

thanks VBA. I've made it work. Sorry Rick,I was looking to get results for each letter separately across few cells>Thanks to VBA it's done.Thank you.

 

[m1ner]

One more thing.If I was to leave cell blank it does not update final count.I have google it and I have found something like that:=SUMPRODUCT(--LEN(B3:B14)-LEN(SUBSTITUTE(UPPER(B3:B14),"L",""))),--(B3:B14<>"")) but its not working.How to make it work?

 

[m1ner]

I have managed to fix it,it should be:=SUMPRODUCT(--(LEN(B3:B14)-LEN(SUBSTITUTE(B3:B14,"L",""))),--(B3:B14<>""))