How would I count the number of occurrences of a name from a list?

Graham Rimell

So I've got a list of names, many of which are duplicates.

What i'd like to do is to first:

-From the main list, create a second column of unique names with all the duplicates removed

-Have a 'count list which counts how many times each name has occurred.

Like this, but when you populate the Primary column it automatically filters the data for Unique values and Count.

Excel has the 'advanced' feature that can do this, but I'm struggling to duplicate this on Smartsheets.

Thanks.

David Tutwiler

The Counting is pretty easy. Something like:

=COUNTIF([Primary Column]:[Primary Column], Name@row)

This will go through the whole Primary Column range and count the occurrences of the Name at the row you are currently on. Either copy this formula down or make it a Column formula and it should work on all of the names you have listed.

As for extracting the list of unique names, I would direct you to this older discussion that covers it pretty well.

https://community.smartsheet.com/discussion/54816/extracting-a-list-of-unique-values-from-a-list-on-another-sheet

moneil_columbia3

Here is the same formula I am using, however it is not returning the correct values. The issue is that the column (Range 5) contains the name, it does not equal the name. How would I write this formula if I wanted to count how many times a name appears in a field but each field contains several names, not just the one I am searching for?

=COUNTIF({Overall Project directory Range 5}, Name@row)

David Tutwiler

=COUNTIF({Overall Project directory Range 5}, CONTAINS(Name@row, @cell))

Paul Newcome

@David Tutwiler Your fingers were moving too fast with CONTAINS.

=COUNTIF({Overall Project directory Range 5}, CONTAINS(Name@row, @cell))

I do the same thing all the time with COUTNIFS. 🤣

David Tutwiler

@Paul Newcome Ah! I hate when I do that. Thank you for the catch. I'll try to edit it on mine as well.