Excel 2K, multiple values in one cell, sumproduct 2

[goopit]

Hello All,

I was recently sent a file to work with which, at times, contains multiple values in one cell. For example, one cell may have "Cynthia, Suzy, Susan G.".

I've been asked to run a total of how many instances one particular name shows up within a given range, but there really is no way to write a SUMPRODUCT or something to where it will report back all instances of "contains 'Suzy'", let's say, right? It's easy to get it to retrieve a name if it is the only one in the cell, but if there are additional ones, not much can be done as far as I can tell.

If, however, any of you wonderful folks out there do know of a fix, I'd sure love to hear it. Thanks, as always, for any and all assistance.

Scott

 

[SkipVought]

Hi,

I'd use Data/Get external data/New database Query -- Excel Files -- YOUR WORKBOOK -- YOUR SHEET...

and query using a CONTAINS criteria.

Skip,
[sub]
[red][/red]
[/sub]

 

[goopit]

Lovely idea, Skip. Thanks so much. Have a star.

Scott

 

[SkipVought]

Could even use the AutoFilter - CONTAINS...

Select the column and use the COUNT feature, for a kwik count.

Skip,
[sub]
[red][/red]
[/sub]

 

[KenWright]

I like the query option, but just so you know it can be done with SUMPRODUCT:-

=SUMPRODUCT(--ISNUMBER(SEARCH("*Suzy*",'D:\4mydata\[Test Book1.xls]Sheet1'!$A$1:$A$7)))

Regards
Ken............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[SkipVought]

Ken,

Thanks! ==>*

Skip,
[sub]
[red][/red]
[/sub]

 

[KenWright]

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[thegrover]

If you want you can use the "text to columns" function and create a column for first names and a second column for last names.

 

[KenWright]

Except the example data shows that the data is not of that format - it is a combination of different people's names.

Regards
Ken...............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[SkipVought]

hrump! A minor detail!

Skip,
[sub]
[red][/red]
[/sub]

 

[KenWright]

LOL

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------