Excel experts

[Grassshoppa]

Spreadsheet with hundreds of names. Each name is listed multiple times. Some 7, some 10, some 20, etc. I need Excel to count only the number of names that have 20 rows for each name.

Example, Joe Smith's name is listed on 20 rows. I need Excel to count Joe Smith as "1".

John Smith's name is listed on 20 rows. I need Excel to count John Smith as "2" and so forth.

If Cliff Smith's name is listed 10 times, I don't want Excel to count that one at all.

Any ideas how to do this?

 

[Spounman]

Have you tried doing a V lookup?
https://spreadsheeto.com/vlookup/

 

[boingyman]

You can use COUNTIF, but there are probably easier methods depending on the complexity.

 

[Jack Reacheround]

I can’t remember the exact formulas for this, but you could create a column that sums up the total of each name.
Then, I’m the next column make it so any number under 20 gives you one value, and any equal to or over 20 gives you another value.

 

[zapataxiv]

can you write a Google app script?

 

[RJ43]

I’d probably create a second column and assign everyone a value of one in that column. Create a pivot table with the names as rows, and then a Count field on the number column. Short highest to lowest in the count, omit those below 20, and then add a third column assigning sequential numbers. Should take about 5 minutes tops.

 

[lsa]

I think we better ask @Clippy

I mean he was born in excel

 

[Get To Da Choppa]

Spreadsheet with hundreds of names. Each name is listed multiple times. Some 7, some 10, some 20, etc. I need Excel to count only the number of names that have 20 rows for each name.

Example, Joe Smith's name is listed on 20 rows. I need Excel to count Joe Smith as "1".

John Smith's name is listed on 20 rows. I need Excel to count John Smith as "2" and so forth.

If Cliff Smith's name is listed 10 times, I don't want Excel to count that one at all.

Any ideas how to do this?

There are many ways to do this, and here's one that I think is straightforward and doesn't involve complex formula.

1. Copy and paste the names in a separate column, remove duplicates to get the unique names

2. From that list of unique names, do a countif in a column next to it to find out how many times the names show up

3. Filter out the countif columns to display results of over 20, and you'll get the corresponding names with counts of 20 or more

4. Get back on Sherdog and shitpost more

 

[HeffDoesWant]

Countif then countif the results.

 

[AristotleAmadopolis]

thought this was a new subspecies of incels for a second

 

[Ballsaque]

Spreadsheet with hundreds of names. Each name is listed multiple times. Some 7, some 10, some 20, etc. I need Excel to count only the number of names that have 20 rows for each name.

Example, Joe Smith's name is listed on 20 rows. I need Excel to count Joe Smith as "1".

John Smith's name is listed on 20 rows. I need Excel to count John Smith as "2" and so forth.

If Cliff Smith's name is listed 10 times, I don't want Excel to count that one at all.

Any ideas how to do this?

=COUNTIF(A1:A300,"Johnnyboy")

Do this for each name. The first input is the range you want to search.

 

[davidlemonparty]

Dude chatgpt was born for this sort of thing. You'll have an answer in a second

 

[GSP_37]

Could have swore there was such thing as count unique.

 

[Grassshoppa]

There are many ways to do this, and here's one that I think is straightforward and doesn't involve complex formula.

1. Copy and paste the names in a separate column, remove duplicates to get the unique names

2. From that list of unique names, do a countif in a column next to it to find out how many times the names show up

3. Filter out the countif columns to display results of over 20, and you'll get the corresponding names with counts of 20 or more

4. Get back on Sherdog and shitpost more

This worked great. Thank you good sir.