Formula Help - Counting Unique Markets

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?


Best Answer

  • SteyJ
    SteyJ ✭✭✭✭✭✭
    edited 12/18/23 Answer ✓

    This should work

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

    Jacob Stey

Answers

  • SteyJ
    SteyJ ✭✭✭✭✭✭

    try this:

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

    Jacob Stey

  • MindyM
    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
    SteyJ ✭✭✭✭✭✭
    edited 12/18/23 Answer ✓

    This should work

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

    Jacob Stey

  • MindyM
    MindyM ✭✭

    That worked! Thank you so much!

  • MindyM
    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
    SteyJ ✭✭✭✭✭✭
    edited 12/18/23

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


    Sincerely,

    Jacob Stey

  • MindyM
    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?

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


    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

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

  • 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?

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

  • MindyM
    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!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!