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

  • Thread starter Thread starter Guest
  • Start date Start date
G

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?
 
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")
 
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
 
Be careful using formulas with SUBSTITUTE as this function is case-sensitive
and "fam" is not equal to "Fam"
 
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
 
Back
Top