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

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

StephenCrump

Well-known Member
Joined
Sep 18, 2013
Messages
3,822
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,201
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,201
=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,113,875
Messages
5,544,808
Members
410,635
Latest member
phoenix7771
Top