How to get a count of every time a name appears on another smartsheet

Abner

I have 2 smartsheets:

• the first one: has a list of names and the name appears multiple time based on the number of certifications that you have. If a user have 5 certifications, the name would appear 5 times.
• I have a second smartsheet that I need to have the user name and the count of how many times that name appears.

Thank you for your help

Frank S.

Greetings @Abner,

I would approach your problem in the following way:

Step 1: Create a helper column on sheet 2; name something that works within your naming conventions. In this column, place numbers from 1 to the number of unique names you have in sheet 1. We can always add or remove numbers as needed.

Step 2: Create a user name column on sheet 2 and use the following formula:

=IFERROR(INDEX(DISTINCT(COLLECT({Name of the column on sheet 1 where names are located}, {Name of the column on sheet 1 where names are located}, AND(@cell <> "", ISTEXT(@cell)))), [Name of Column from Step 1]@row), "")

This will pull all of the unique names from sheet 1 into the column on sheet 2 and it ignores any blank entries.

Step 3: Create a column on sheet 2 to count the number of times the name appears. The formula might look something like this:

=COUNTIF({Name column on sheet 1}, = [column name step 2] @ row)

I hope this helps and resolves your problem.

Abner

Hey Frank,

Thank you for responding to my issue and I really appreciate it. I am confused to where do I reference sheet 1. I see that I am pulling content from sheet 1 but the formula doesn't have the name for sheet1. am I missing something? pretty new to smartsheet