Formula Help - Counting Unique Markets

MindyM

I'm trying to duplicate a report I have in Excel into Smartsheet. I need to count how many unique values I have in the MARSHA Code column. I could have duplicates and I need to know the total number of unique markets that attended. What formula would I use?

SteyJ

This should work

=COUNT(DISTINCT(COLLECT([MARSHA Code]:[MARSHA Code], 
[Franchise Company]:[Franchise Company], "Aimbridge Hopsitality")))

SteyJ

try this:

=COUNT(DISTINCT([MARSHA Code]:[MARSHA Code])

MindyM

Thank you for responding so quickly @SteyJ! That worked but I do have another element I need to add in. I only want to see the locations that attended from a certain Franchise Company (Aimbridge Hospitality). How can I add that condition in?

SteyJ

This should work

=COUNT(DISTINCT(COLLECT([MARSHA Code]:[MARSHA Code], 
[Franchise Company]:[Franchise Company], "Aimbridge Hopsitality")))

MindyM

That worked! Thank you so much!

SteyJ

https://community.smartsheet.com/discussion/comment/410111#Comment_410111

anytime!

MindyM

I actually have another question. I tried to apply what you sent me and it didn't work right. If I needed to add one more element, how would I do that? I need to know how many Franchise:MSB hotels from Aimbridge Hospitality there are.

This is what I tried but it isn't working:

=COUNT(DISTINCT(COLLECT([FS/MSB/Franchise]:[FS/MSB/Franchise],="Franchise:MSB")[Franchise Company]:[Franchise Company], ="Aimbridge Hospitality")))

SteyJ

Does this work?

=COUNT(DISTINCT(COLLECT([FS/MSB/Franchise]:[FS/MSB/Franchise], 
[Franchise Company]:[Franchise Company], "Aimbridge Hopsitality",
[FS/MSB/Franchise]:[FS/MSB/Franchise], "Franchise:MSB")))

I think you can also do it like this

=COUNT(DISTINCT(COLLECT([MARSHA Code]:[MARSHA Code], 
[Franchise Company]:[Franchise Company], "Aimbridge Hopsitality",
[FS/MSB/Franchise]:[FS/MSB/Franchise], "Franchise:MSB")))

MindyM

The count isn't coming back correctly for some reason. Its saying 153 when I have 219 if I do a manual count. Any ideas as to why that would be happening?

Genevieve P.

Hi @MindyM

Is it possible that some of your cells have slightly different data where "Franchise Company" or "FS/MSB/Franchise" are spelled differently? The formula will only count the distinct values in the Code column if the two other values match letter-for-letter.

You can check this by testing two different COUNTIF functions against a filter:

=COUNTIF([Franchise Company]:[Franchise Company], "Aimbridge Hopsitality")

=COUNTIF([FS/MSB/Franchise]:[FS/MSB/Franchise], "Franchise:MSB")

MindyM

Hi @Genevieve P. - Thank you for the assistance! I thought of that and checked. All of the data in those cells are matching since it's a drop down.

I updated the formula because I realized there was an additional field I didn't need but it's still not working properly. Now it's coming up with 1. I'm trying to determine how many attendees came from Aimbridge Hospitality MSB hotels and then in another equation, I want to figure out how many attendees came from Aimbridge Hospitality FS hotels. The equation below isn't working. Do I have something incorrect?

=COUNT(DISTINCT(COLLECT([Franchise Company]:[Franchise Company], "Aimbridge Hospitality", [FS/MSB/Franchise]:[FS/MSB/Franchise], "Franchise:MSB")))

=COUNT(DISTINCT(COLLECT([Franchise Company]:[Franchise Company], "Aimbridge Hospitality", [FS/MSB/Franchise]:[FS/MSB/Franchise], "Franchise:FS")))

Genevieve P.

Hi @MindyM

In these new formulas we're not indicating what column we want it to count distinct (unique) values from. Think of the COLLECT function like a filter... the way it works is that you first list a column to perform an action on (in this case we're going to count the cells in a specific column). Then you list each column and criteria like you would select in a filter:

=COUNT the DISTINCT or unique values, based on this COLLECTed list, filtered by my selections

So in your case, you'll need to list the "attendees" column first, if that's the one you have unique values in:

=COUNT(DISTINCT(COLLECT(unique values column reference, [Franchise Company]:[Franchise Company], "Aimbridge Hospitality", [FS/MSB/Franchise]:[FS/MSB/Franchise], "Franchise:MSB")))

In @SteyJ's example above, the column to count unique entries in was titled [MARSHA Code]. Is that correct? If so, it would go at the front:

=COUNT(DISTINCT(COLLECT([MARSHA Code]:[MARSHA Code], [Franchise Company]:[Franchise Company], "Aimbridge Hospitality", [FS/MSB/Franchise]:[FS/MSB/Franchise], "Franchise:MSB")))

Does that make sense?

MindyM

Thank you so much @Genevieve P. for the explanation and clarification! That really helped me understand what I was doing wrong. Appreciate the assistance!