Find and Count

  • Thread starter Thread starter Kellia
  • Start date Start date
K

Kellia

I have a cell with a bunch of information (combinations of dates, times and
names). I'd like excel to look through the cell, find a name(s) and output
the number of times the name(s) appears in that cell.

Count with Vlookup?
 
For example, cell X3 has the data: 3/18/2009 6:58 PM by Cole, Jane, 2/3/2009
1:04 PM by Sande, Kellia, 9/26/2008 8:44 PM by Kep, Carole, 11/25/2008 4:14
PM by Soly, Suzie, 3/9/2009 7:27 PM by Sande, Kellia

I need it to search through the data and count the number of times "Kellia"
appears.

I'm currently using ISNUMBER combined with SEARCH to return a True/ False
verdict.

Thanks for taking my case
 
If you want to count the number of cells that contain Kellia, you could use a
formula like:

=countif(a:a,"*kellia*")

or if B1 contained Kellia:
=countif(a:a,"*"&b1&"*")

The asterisk are wildcards that represent any amount of characters.

If Kellia could show up multiple times in a cell and you wanted to determine the
number of times that it appeared in that single cell:
=(len(a1)-len(substitute(upper(a1),upper("kellia"),"")))/len("kellia")

=substitute() is case sensitive, so the =upper() functions makes sure that both
strings are uppercase and that means this counts the number of Kellia, KELLIA,
KelLIA, KELLIa, ... appears in the cell.

If you have a range of those cells with possible entries to count:

=sumproduct((len(a1:a10)-len(substitute(upper(a1:a10),upper("kellia"),"")))
/len("kellia"))

Adjust the ranges to match. But you can use the entire column only if you're
using xl2007.
 
Back
Top