Counting Unique Values in a Column of another sheet

edited 12/09/19 in Formulas and Functions

Hi there,

I'm looking to count the number of unique IDs in another sheet I'm referencing (that I cannot edit), and put the count by each ID in another sheet I'm using. I'd appreciate any tips that could help me especially keeping in mind I cannot edit the reference sheet (There are examples similar to this but they require editing that sheet). Thanks!


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    How many unique ID's do you have? Will this number change? Are you able to provide some screenshots? Even though you cannot edit, would someone that can edit be able to add helper columns to the other sheet for you?

  • Thousands of IDs that won't change (some are the same), but more will be added and I need what I'm working on to auto-update with new entries. Unable to provide screenshots, but it's possible I could get someone to add a helper column and hide it, but I would rather not. Are there even possible work-arounds?

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    I can only think of one workaround at the moment, but...


    It is extremely cumbersome to build and doesn't easily auto-update for added entries. I will keep thinking on it though.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Are all of the ID's in the same column on the original sheet?

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    edited 06/05/19


    I hade to adjust the formulas in the Unique column to include an IFERROR. Unused rows throw an Invalid Value error which was pulling through into the COUNT1 formula.






    Ok. Here is a solution. It will need some tweaking to accommodate how many rows you have, but I'd prefer to test on a smaller scale for now to make sure this solution (with tweaks for scale) will even work for you.


    So here goes...


    I have 5 columns as follows:

    Count (Text/Number)

    Unique (Checkbox)

    List (Text/Number)

    Comma (Text/Number)

    String (Text/Number)


    Count1: =COUNTIFS(Unique:Unique, 1)

    Unique1: =IFERROR(IF(COUNTIFS(List$1:List@row, List@row) = 1, 1), "")

    List1: =VALUE(LEFT(String@row, FIND(",", String@row) - 1))

    Comma1: 0

    String1: =JOIN(COLLECT({Original Sheet ID Column}, {Original Sheet ID Column}, @cell <> ""), ",") + ","


    Count2: Blank

    Unique2: Dragfill from Unique1

    List2: Dragfill from List1

    Comma2: =LEN(LEFT(String1, FIND(",", String1)))

    String2: =RIGHT(String$1, LEN(String$1) - Comma@row)


    Count3: Blank

    Unique3: Dragfill from Unique2

    List3: Dragfill from List2

    Comma3: =LEN(LEFT(String$1, FIND(",", String$1, Comma2 + 1)))

    String3: Dragfill from String2


    From there you would dragfill row 3 for all columns all the way down until you have covered as many rows as you expect to have. You don't want to stop at where you are now because it won't update when new rows are added to the original sheet.

    If you already have thousands and anticipate thousands, you may as well dragfill to the max number of rows of 5,000.



    Here's that catch that will require some accommodation:


    A cell can only have 4,000 characters in it. Therefore to accommodate 5,000 rows with a single digit ID, we would need to split the data in String1 out into at least 2 (probably more for your specific case) separate cells to avoid going over that 4,000 character limit.


    If you test this solution on a small scale and like it, let me know, and we will work on scaling this up for you.

  • That's quite the solution...I'll give it a shot! Thanks for the help!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!