Need help on Count function!!!

  • Thread starter Thread starter MJ
  • Start date Start date
M

MJ

Anyone! I need assistance for the following issue. I
know how to do a count for number of occurences of a word
in a given range by doing a =COUNTIF(A1:A10,"*ZT-*") which
would give me a count of the number of times ZT- appears
in that range. HOWEVER my problem is that "ZT-" can
appear multiple times in 1 cell (such as "VM-03246-01/ZT-
03310-31, NK-03291-01 & ZT-01196-20") - as you can see,
the letters "ZT" appears twice, but the =COUNTIF function
would see only 1 occurence rather than 2. I need to get
an accurate count of how many times "ZT" appears in that 1
cell. Can anyone offer some assistance? Am I using the
wrong function? Much Appreciated!! -MJ
 
Interesting. I'll try that.

Thanks much!!!

-MJ
-----Original Message-----
Hi
try
=SUMPRODUCT(LEN(A1:A10)-LEN(SUBSTITUTE(A1:A10,"ZT- ","")))/LEN("ZT-")


--
Regards
Frank Kabel
Frankfurt, Germany



.
Click to expand...
 
Didn't work. It gave me a total of 8 when there was only
3 occurances... Why did it give me 5 more than expected?

Thanks,

Mike
 
Didn't work. It gave me a total of 8 when there was only
3 occurances... Why did it give me 5 more than expected?

Thanks,

Mike
 
Hi
could you post some example rows of your data? (plain text - no
attachment please)
 
Probably because there were multiple occurrences in one or more cells.
There is now way on earth that formula can return more occurrences than
there are
Note that if there was consonant filled Polish names that contained zt like
Schwrevienzt it will count that as well<g>
 
Back
Top