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

Options

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

Answers

  • Frank S.
    Frank S. ✭✭✭✭✭✭
    Options

    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.

    Frank Smith, PMP

    Assistant Director | IT Special Projects Mgr.

    Oregon Parks & Recreation Department

    If my response helps, please mark it as an accepted answer. 😎

  • Abner
    Abner ✭✭
    Options

    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!