Count values in a cell

[terrydunne]

Question
In a column where each cell has multiple text values separated by 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 the spreadsheet's DATA worksheet, the project names are listed in the first column and the projects' status---In progress, On hold, Red flag, Complete---is entered in the Status column in the same row as the project name. On another worksheet, I use the COUNTIF 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 is the E column, I will have a series of functions that say =COUNTIF(DATA!E2:E100,":$In Progress"), =COUNTIF(DATA!E2:E100,":$On Hold"), =COUNTIF(DATA!E2:E100,":$Red flag"), etc, to get the numbers to 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 the cells in this column as "Bill, George" and "Tom, Bill, George", and "Tom, George, Mary, Bill". I want to count the number of times that, say, Bill has appeared in the Resources column for the purpose of counting how many projects Bill is on. To do this, I have tried to use the COUNTIF function in combination with separating the values in the cell with commas. This doesn't work. Is there any way of separating 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 IF functions to return a value of TRUE if, say, Bill appears, or George appears, etc, but I don't know how to count the results to get the 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" <[email protected]> wrote
in message news:[email protected]...
>
> _QUESTION_
> In a column where each cell has multiple text values separated by
> 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 the
> spreadsheet's DATA worksheet, the project names are listed in the first
> column and the projects' status---*-In progress-*, *-On hold-*, *-Red
> flag-*, *-Complete-*---is entered in the *Status * column in the same
> row as the project name. On another worksheet, I use the COUNTIF
> 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 is
> the E column, I will have a series of functions that say
> =COUNTIF(DATA!E2:E100,":$In Progress"), =COUNTIF(DATA!E2:E100,":$On
> Hold"), =COUNTIF(DATA!E2:E100,":$Red flag"), etc, to get the numbers to
> 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 the
> cells in this column as "Bill, George" and "Tom, Bill, George", and
> "Tom, George, Mary, Bill". I want to count the number of times that,
> say, Bill has appeared in the Resources column for the purpose of
> counting how many projects Bill is on. To do this, I have tried to use
> the COUNTIF function in combination with separating the values in the
> cell with commas. This doesn't work. Is there any way of separating
> 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 IF
> functions to return a value of TRUE if, say, Bill appears, or George
> appears, etc, but I don't know how to count the results to get the
> numbers to build a chart.
>
> Any advice is welcome.
>
>
> --
> terrydunne
> ------------------------------------------------------------------------
> terrydunne's Profile:
http://www.excelforum.com/member.php...o&userid=25160
> View this thread: http://www.excelforum.com/showthread...hreadid=386610
>

[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!
==============================================


"terrydunne" <[email protected]> wrote
in message news:[email protected]...

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
------------------------------------------------------------------------
terrydunne's Profile:
http://www.excelforum.com/member.php...o&userid=25160
View this thread: http://www.excelforum.com/showthread...hreadid=386610

[terrydunne]

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