Count unique names across five columns

pageella

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!

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")

pageella

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

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 <> "")))

pageella

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.

pageella

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

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.

pageella

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

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)))

pageella

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

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.