Count unique names across five columns


I need to count the number of UNIQUE ambassadors across all columns.

I would then like to know how many ambassadors we have per country as well.



  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    To go across the columns on all rows you would use:

    =COUNT(DISTINCT(COLLECT([Ambassador - A]@row:[Ambassador - E]@row, [Ambassador - A]@row:[Ambassador - E]@row, @cell <> "")))

    Then to get the total by country, you would use a SUMIFS to add the rows in the column containing the above formula based on the Country column (assuming this is on another sheet).

    =SUMIFS({Total Column}, {Country Column}, @cell = "United States")

  • Thanks. I think that works to count unique ambassadors in each row, but it isn't counting unique across rows.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    I must have misunderstood. Are you trying to count how many unique entries there are on all rows in all columns as a single count? If so, remove each "@row" instance (but leave the "@cell") so that you are referencing every row in every column.

    =COUNT(DISTINCT(COLLECT([Ambassador - A]:[Ambassador - E], [Ambassador - A]:[Ambassador - E], @cell <> "")))

  • Yes, basically the sheet is a list of names (each row is a site and a site can have up to 5 people - hence the 5 columns) and I need to know how many people we have registered (overall and per country)

    I assume this would be a sheet summary formula as opposed to a column formula.

    This returns 1911, however if I export the sheet to excel and 'Remove duplicates' I get 1506.

  • @Paul Newcome Sorry to follow up .. I wonder if you could help with the above?

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Excel's remove duplicates feature can get a little finicky sometimes removing entire rows instead of treating each column as a separate data set.

  • Ah yes when I put into one column in excel I get it!

    How do I add an IF to the formula, eg I only want to count if 'Include Directory' column is checked

    I tried: =COUNTIFS(DISTINCT(COLLECT([Ambassador - A]:[Ambassador - E], [Ambassador - A]:[Ambassador - E], @cell <> "")), [Include Directory]:[Include Directory], 1)

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    edited 05/25/23

    That makes things more complicated. You can't have a range covering multiple columns and a range covering a single column all in the same function.

    The easiest solve for this is to create additional checkbox columns (that can be hidden after setting up) and a basic cell reference to check the box if the [Include Directory] column is checked.

    =[Include Directory]@row

    You will need one for each of the contact columns. Then your formula would look like this:

    =COUNT(DISTINCT(COLLECT([Ambassador - A]:[Ambassador - E], [Ambassador - A]:[Ambassador - E], @cell <> "", [First Helper Checkbox]:[Last Helper Checkbox], @cell = 1)))

  • AMAZING! Thank you so much for all your help Paul!

    One final question (I promise) I am hoping to calculate this based on the Country column so I know how many ambassadors in each country, is this possible?

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    It is possible. You would need to do the same thing for the country column as was done with the checkbox column so that all range sizes match.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!