Distinct Count


I am going round and round trying to get a total distinct count from the ID Admits-Year column. I have tried formula.

=COUNT(DISTINCT(COLLECT([ID ADMITS - Year]:[ID ADMITS - Year],)) and I am getting a number 1 when I have 2 ID's that show up multiple times.

Let me give more background information, I made the helper column ID ADMIT-CY to count how many times an ID w/year was displayed in ID ADMITS-Year. Two ID's show up multiple times within the year so I need to count only those two ID's that show up multiple times but not every time they show up just the one time they show up in the year.

I need a distinct count of the ID-Year in this column I have a combined with the ID & year

Please assist!


  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭

    Hi @Lisa Coleman ,


    =COUNT(DISTINCT(COLLECT([ID ADMITS - Year]:[ID ADMITS - Year], [ID ADMITS - Year]:[ID ADMITS - Year], istext())))

    Work? If not, are you getting an error or the wrong answer?


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • @Mark Cronk thank you for trying to assist me. No error just the wrong answer!

    I am getting 1 when I want a total count of each ID Admit-Year that shows up more than 3 times so basically there should be 2 because 123456-20 shows up 4 times and 248657-2020 shows up 5 times.

  • Nic Larsen
    Nic Larsen ✭✭✭✭✭✭

    Does this work:

    =COUNT(DISTINCT(COLLECT([ID ADMITS - Year]:[ID ADMITS - Year], [ID ADMIT-CY (hide)]:[ID ADMIT-CY (hide)], >=3)))

  • L_123
    L_123 ✭✭✭✭✭✭
    edited 01/22/21

    ignoring everything except your summarization:

    I need a distinct count of the ID-Year in this column I have a combined with the ID & year

    would leave you with the simple:

    =count(distinct([ID ADMITS - Year]:[ID ADMITS - Year]))

    if you want to exclude the id's that don't have years associated then you could use a collect with a single criteria saying

    =count(distinct(collect([ID ADMITS - Year]:[ID ADMITS - Year],[ID ADMITS - Year]:[ID ADMITS - Year],not(right(@cell,1)="-"

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!