Count function for words in a cell

[mikeminto]

Hello all,

I run an adult hockey league where we use an excel sheet to upload stats to our website. I am trying to simplify the sheet which I have received from the previous operator.

I was wondering if I can do a count of cells based on text from within a cell. For example, if John is in cell A1, can I get excel to count how many times John comes up in an area without using John in the formula but rather A1? I am trying to avoid putting players names in each formula to help speed up the development process.

Thanks.

[TheCman81]

This is a basic use of COUNTIF but to suit your data I would need to see how it's laid out? Can you post a sample?

Please see attached and let me know if this is what you need

mikemintov1.xlsx

[mikeminto]

mikemintov1.xlsx see attachment, needs to be 2 cells if possible.

[danicae]

You could make a pivot table of the data and put "name" in both the row and the count. I think that should give you the answer you're looking for. Remember to right click on the pivot table to update it if you change your original data.

If you have multiple areas, you might need to make multiple pivot tables (if there are only a few areas) or you need to reformat the data so that each area is a column and the names appear in rows below the columns, then make a pivot table of that whole new set.

[danicae]

Looking at the example, you can also concatenate the first and last names (eg. take | "John" | "Smith" | and make it "John Smith" in one cell), then you can search for that whole combination. You'd be counting if cell value= whole name.