Count unique names across five columns
Hi,
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.
Thanks!
Answers
-
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.
-
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?
-
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)
-
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?
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.7K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!