QTY count of letter in A1:A4

caabyyc

Board Regular
Joined
Oct 24, 2013
Messages
126
Hi, could you please help?
Source: A1:A4,
want to: count total QTY of letter "F"(upper case only) in A1:A4 (formula only please)
correct result should be 4.
:eek:

 A
1F4545
239FF
3345F
45923f

<colgroup><col><col></colgroup><tbody>
</tbody>


:eek:
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

StephenCrump

Well-known Member
Joined
Sep 18, 2013
Messages
3,806
Office Version
  1. 365
Platform
  1. Windows
=sumproduct(len(a1:a4)-len(substitute(a1:a4,"f","")))

Edit: For some bizarre reason, this keeps losing its capitalisation. Use Aladin's version with capital "F".
 
Last edited:

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,192
Try...

=SUMPRODUCT(LEN(A1:A4)-LEN(SUBSTITUTE(A1:A4,"F","")))

Note. SUBSTITUTE is case-sensitive.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,192
=sumproduct(len(a1:a4)-len(substitute(a1:a4,"f","")))

Edit: For some bizarre reason, this keeps losing its capitalisation. Use Aladin's version with capital "F".

The board software sets a formula in lower case if nothing else in addition is typed.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,453
Messages
5,528,875
Members
409,843
Latest member
akostaki
Top