COUNTIF for really specific text strings

[Sammyzue]

Hi all,

I'm afraid I'm not a real Excel whizz so forgive me if I don't explain this well. I have a table that lists out our team projects and which staff are allocated to them. This can be one person or multiple separated by a comma and space. I then want to create a list of the team with a count next to their name of how many projects each person is on, but I have an issue in that two staff members have very similar names.

If this is my range of five projects, how do I count how many projects Ann is assigned to?

Ann
Annalisa
Dave, Ann
Ann, Sarah
Sarah

If Ann is the reference cell at A1 I've tried...

=COUNTIF(Range, "*" & A1 & "*")

but that counts Annalisa as well so I get 4 instead if 3. If I try...

=COUNTIF(Range, A1)

that's exact match and I only get 1.

Any ideas???

Thanks!

[JulianS96]

Hi,
Instead of trying to explain it please attach a sheet with a mock-up of your expected result. Follow the yellow banner at the top.
Cheers

If i understand correctly you could try just =COUNTIF(Range,A1&"*")

[Richard Buttrey]

The obvious first question is why is it necessary to capture the data in such a way?
A more traditional approach, and certainly a more flexible and efficient approach would be to have two columns, one for the Project Reference and the second for the Person's name

So in this example you would have 7 rows. Then using COUNTIF is trivial.

However this is clearly a much simplified example and probably doesn't represent the size of your actual data. We often find that when we give an answer to what is a trivial non representative example when the solution is used with the real world data it doesn't work because of factors like string length and other stuff which is important but hasn't been mentioned.

Therefore upload your real workbook (or at least a cut down copy), and manually add the results you expect to see. Clearly identify which is original data and which are the results and in a note clearly explain how you have arrived at your results.

[BMV]

Please Login or Register  to view this content.

or array formula

Please Login or Register  to view this content.

[Pete_UK]

You could achieve the result you want by this formula:

=SUM(COUNTIF(A1:A5,{"*, Ann","Ann, *","Ann"}))

with your data in A1:A5.

Hope this helps.

Pete

[Sammyzue]

Thanks Pete! I did think of that, but that means I would have to type the names out for each of the 100+ team members. Do you know if there's a way to do that but substitute the actual word Ann for a cell reference, eg A1?

[JulianS96]

If i understand correctly you could try just

Please Login or Register  to view this content.

Hopefully this helps However I think that BMV's solution is best.

[Pete_UK]

Wherever Ann appears in the formula (3 times) you can substitute A1 for it, but you would also need to adjust where the quotes are and add a solidus where necessary, i.e.:

=SUM(COUNTIF(A$1:A$5,{"*, "&A1,A1&", *",A1}))

Hope this helps.

Pete

EDIT: Forget that - you can't use a cell reference in a literal string.

Pete

[BMV]

JulianS96, Pete_UK's solution useful also but both have some restrictions and depend on data and condition. As example if in stand of comma there will be dot or any other symbols the formulas must be changed.

[Richard Buttrey]

I come back to my original question. If someone is typing in seven names some in the same cell why not just type the names in a row of their own. The adjacent Project reference could be a data validation drop down which avoids further typing.

I never cease to be surprised that people complicate data enty from the start and then wonder down the line as their system develops why it becomes harder and harder to analyse it.

[Sammyzue]

HI Richard,

Thanks for your reply. I'm afraid its not my template, its a company wide one so I have no power to change the format, I'm just a data entry drone in this situation trying to make my own life easier by automating a manual process within the formatting confines I've been given.

Sam