Count values in a cell

[terrydunne]

_QUESTION
In a column where each cell has multiple text values separated b
commas, I want to count specific values. Do you know a way to do this?

_BACKGROUND
I'm in charge of a spreadsheet that tracks projects. On th
spreadsheet's DATA worksheet, the project names are listed in the firs
column and the projects' status---*-In progress-*, *-On hold-*, *-Re
flag-*, *-Complete-*---is entered in the *Status * column in the sam
row as the project name. On another worksheet, I use the COUNTI
function to count the data in the Status column (on the DATA worksheet
and build a chart from the results. For example, if the Status column i
the E column, I will have a series of functions that sa
=COUNTIF(DATA!E2:E100,":$In Progress"), =COUNTIF(DATA!E2:E100,":$O
Hold"), =COUNTIF(DATA!E2:E100,":$Red flag"), etc, to get the numbers t
build the chart. This works fine.

_*The_problem*_
Another column titled *Resources * lists, in each cell, the names of
individuals working on a project. Typically, data is entered into th
cells in this column as "Bill, George" and "Tom, Bill, George", an
"Tom, George, Mary, Bill". I want to count the number of times that
say, Bill has appeared in the Resources column for the purpose o
counting how many projects Bill is on. To do this, I have tried to us
the COUNTIF function in combination with separating the values in th
cell with commas. This doesn't work. Is there any way of separatin
these values so the COUNTIF function can pattern match specific values
to find, say, Bill and ignore the others?

If COUNTIF can't be used, I am thinking of trying a series of I
functions to return a value of TRUE if, say, Bill appears, or Georg
appears, etc, but I don't know how to count the results to get th
numbers to build a chart.

Any advice is welcome

 

[RagDyer]

Say that your "Resources" column is A2 to A100.
Say you enter the name your looking to count in C1.

Try this:
=SUMPRODUCT(LEN(A2:A100)-LEN(SUBSTITUTE(A2:A100,C1,"")))/LEN(C1)

NOW ... the caveat:
IF you enter "Fred" in C1, this will *also* count:
Freddy
Freddie
Frederico
Fredric
Frederick
BUT, it will *NOT* count:
Alfred
Wilfred

So watch out for the case sensitivity!
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

 

[terrydunne]

Thanks for the help. It took me awhile to get back to you because I
needed to update the project status spreadsheet about which I messaged
this user forum. If you recall, I wanted to count the number of
projects each person had in my department. I did not understand how to
use the solution you proposed so I used a crude solution of creating a
separate table, outside the main data table, that paired individual's
names in column headers with project names in row headers and then
inserted the word "Yes" where project and name met (if a particular
person was actually associated with a particular project). I then used
the COUNTIF function to count the instances of "Yes" to come up with a
project count for each individual. From that data I created an
impressive pie chart that was received well.

However, I would like to do this more elegantly and more dynamically.
What I don't understand about the solution you sent is where to put,
and how to count, the pattern match I am looking for. If you recall, I
am looking to match, say, the name "Roger" in a column titled
"Resources" (E2:E100) where the individual cells may have entries like
"Jim, Mary, Roger, Joe" and then count the number of times "Roger"
appears in the column and create a pie chart from the results.

To be more clear, if only the name "Roger" appeared in the cell it
would be an easy matter to count the instances of "Roger" in the
Resources column by using the COUNTIF function in a separate table that
referenced the Resources column. It's the fact that "Roger" is paired
with other names that makes the COUNTIF function useless and requires a
more sophisticated solution.

I appreciate your help in this matter and regret that I don’t
understood the solution you sent. If you can provide any additional
help, it is certainly welcome.

Thanks,
Terry

 

[RagDyeR]

The formula I suggested can be entered into any unused cell.

It assumes that the range that contains your multiple name entries is A2 to
A100.

Change that to E2:E100, which you stated here is your current range.
Do that change in both places in the formula.

The formula also assumes that you will enter the name you're looking to
"match" into cell C1.
That you can change to whatever cell you will use for that purpose.

This formula is case sensitive, so you must enter "Roger" in C1 *exactly* as
it's entered in your data column (upper case R).

Try this out and see if it works for you.

A formula that works around the case sensitivity, *BUT* counts *any* name
that might *contain* the looked for name (Fred will count frederick, alfred,
wilfred, freddy, ... etc.) is:

=SUMPRODUCT(LEN(E2:E100)-LEN(SUBSTITUTE(UPPER(E2:E100),UPPER(C1),"")))/LEN(C
1)

Does this clear up your questions?
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


in message
Thanks for the help. It took me awhile to get back to you because I
needed to update the project status spreadsheet about which I messaged
this user forum. If you recall, I wanted to count the number of
projects each person had in my department. I did not understand how to
use the solution you proposed so I used a crude solution of creating a
separate table, outside the main data table, that paired individual's
names in column headers with project names in row headers and then
inserted the word "Yes" where project and name met (if a particular
person was actually associated with a particular project). I then used
the COUNTIF function to count the instances of "Yes" to come up with a
project count for each individual. From that data I created an
impressive pie chart that was received well.

However, I would like to do this more elegantly and more dynamically.
What I don't understand about the solution you sent is where to put,
and how to count, the pattern match I am looking for. If you recall, I
am looking to match, say, the name "Roger" in a column titled
"Resources" (E2:E100) where the individual cells may have entries like
"Jim, Mary, Roger, Joe" and then count the number of times "Roger"
appears in the column and create a pie chart from the results.

To be more clear, if only the name "Roger" appeared in the cell it
would be an easy matter to count the instances of "Roger" in the
Resources column by using the COUNTIF function in a separate table that
referenced the Resources column. It's the fact that "Roger" is paired
with other names that makes the COUNTIF function useless and requires a
more sophisticated solution.

I appreciate your help in this matter and regret that I don’t
understood the solution you sent. If you can provide any additional
help, it is certainly welcome.

Thanks,
Terry

 

[terrydunne]

Thank you for the very helpful reply! Your solution is exactly what
want and, now that you've explained it further, I'm sure I can get i
to work--Terr