# Counting characters

#### m1ner

##### New Member
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

### Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
=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", "")))

Last edited:
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.

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"

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?

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.

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?

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

Replies
5
Views
315
Replies
5
Views
303
Replies
11
Views
2K
Replies
7
Views
232
Replies
5
Views
165

1,196,307
Messages
6,014,570
Members
441,828
Latest member
cofracr

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back