Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find and Count
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find and Count
You will need to post some sample data
"Kellia" wrote: 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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find and Count
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 "Mike H" wrote: You will need to post some sample data "Kellia" wrote: 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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find and Count
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. Kellia wrote: 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 "Mike H" wrote: You will need to post some sample data "Kellia" wrote: 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? -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count to find the number of 100% 90% 80% in a column | Excel Discussion (Misc queries) | |||
Find and count (sumproduct) | Excel Worksheet Functions | |||
Find and count occurrances | Excel Discussion (Misc queries) | |||
Can't find topic for count | Excel Discussion (Misc queries) | |||
How do i count numbers and letters to find a total count of all | Excel Worksheet Functions |