Autofill with same pattern

Is there a way to autofill a formula using COUNTIF (or maybe other variables) which has the same text on Excel 2016?

For example if I have the data in below rows:
John Smith
Dave Brown
Mark Jones

And I want to search in a range for those I could put =COUNTIF(A:A, "Smith")
In the row below I would want to put =COUNTIF(A:A "Brown")

If I use autofill it would just fill it all in with Smith. Is there any way it can copy the rule from above? i.e. keep the cells the same but recognise to change the word in quotes. At the moment I just autofill and overtype each word in quotes which can take a while.

Happy to post an example if needed, which it probably will be! As I said at the start I think I've seen it before when it highlights it in grey?

Use a formula like this in B1:

=IF(COUNTIF(A$1:A1,A1)=1,COUNTIF(A:A,A1),"")

then copy down. It will give a count of the name in column A only on the row where the name is encountered for the first time.

Hope this helps.

Pete

Definitely on the right lines. I've attached an example which might help more as it shows the names are in different columns, and the key has their first and surnames in, rather than just surname.

I think you need to explain your attachment a bit more. What do the columns represent? The first 3 columns have the same name copied down but other columns have two or more names in them, some of which seem to spill from one column to the next. What is the purpose of the arrow-down symbol at the end of some of the names? What are you trying to achieve here?

Pete

The columns are football team line-ups in order, so it's 1-11.

The arrows indicate that the player was substituted. I used it because when I do COUNTIF I end up with the formula =COUNTIF(A:K, "Porter↓"). If a player starts I have: =COUNTIF(A:K, "*Porter*") so it picks up whether it has an arrow next to its name.

Therefore the formulas sent work perfectly for the players that start which is brilliant, but it hasn't collected the info for those that are substituted.

i.e. if the list says:
Porter
Porter
Porter↓
Porter
Porter

For starts with the formula it would read 4 instead of 5, when it should be 5.

The main thing I'm trying to achieve is using autofill where I don't have to copy and paste about 30 different names! So it's not a life-changer but quite handy.