Need to know the number of times "fam" appears in a list of cells

[Guest]

In a large census of data cell A1 through cell A1000; some of these cells
contain the letters "fam" some do not, without have to count, is there a
formula?

 

[Guest]

Try this
=COUNTIF(A1:A1000,"=fam")

 

[JE McGimpsey]

One way:

Assuming only interested in one count per cell:

=COUNTIF(A1:A1000,"*fam*")


If want to count multiple instances of "fam" within a cell:


=SUM(LEN(A1:A1000),-LEN(SUBSTITUTE(A1:A1000,"fam","")))/LEN("fam")

 

[Guest]

If "fam is the only thing in the cells, either of the two two methods
proposed will work.
if Fam is a portion of the cell entry try
=sum(if(len(A:A)-len(substitute(A:A,"Fam",""))>1,1,0))
etered as an array control-shift-enter

 

[JE McGimpsey]

Should have mentioned that this needs to be array-entered...

 

[Tyro]

Be careful using formulas with SUBSTITUTE as this function is case-sensitive
and "fam" is not equal to "Fam"

 

[Peo Sjoblom]

That can be easily fixed

=SUMPRODUCT(LEN(A1:A1000)-LEN(SUBSTITUTE(LOWER(A1:A1000),"fam","")))/LEN("fam")

however maybe the OP was specific enough to want fam and not Fam or FAM

--


Regards,


Peo Sjoblom