I am having an issue with Count(Distinct(Collect returning 1

The formula doesn't error out but produces a 1:

=COUNT(DISTINCT(COLLECT({Server}, {Installation_Date}, YEAR(@cell) = YEAR(TODAY() - 3), {Installed_Date}, MONTH(@cell) = 7)))

I have a list of servers, the hotfixes, and the date the hotfixes were installed. So 1 server may have multiple hotfixes applied during a month/year. I am trying to count the distinct number of servers that had a hotfix applied during a certain month/year. Any idea where I am going wrong? I tried to look through several responses of the same question but still no luck.

Best Answer


  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭


    =COUNT(DISTINCT(COLLECT({Server}, {Installation_Date}, YEAR(@cell) = YEAR(TODAY())- 3), {Installed_Date}, MONTH(@cell) = 7)))


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

  • Brandis Smith
    Brandis Smith ✭✭✭
    edited 07/07/21

    Hi Mark -

    I dropped it in as is, I got #UNPARSEABLE.

    I also tried to remove the extra parenthesis at the end, but it still returned 1.

    I also tried removing the parenthesis after the 3:

    =COUNT(DISTINCT(COLLECT({Server}, {Installation_Date}, YEAR(@cell) = YEAR(TODAY()) - 3, {Installed_Date}, MONTH(@cell) = 7)))

    And still got 1.

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭

    Hi Brandis,

    You're using different names for your [installedOn] column reference - installation_date and installed_date. Confirm both ranges go to the same date column.

    Count(distinct(collect())) should work. Try adding join to put a comma between each value before counting:

    =COUNT(JOIN(DISTINCT(COLLECT({Server}, {Installation_Date}, YEAR(@cell) = YEAR(TODAY()) - 3, {Installed_Date}, MONTH(@cell) = 7)),", "))

    Any luck?


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

  • Sorry, that was a typo on my end. I went back to verify the correct reference for the year and month {Installation_Date}. I also confirmed that there are 17 unique servers with multiple hotfixes with an installation date of July 2018 in my sheet.

    The following is what I've tried and all produce 1.


    =COUNT(DISTINCT(COLLECT({Server}, {Installation_Date}, YEAR(@cell) = YEAR(TODAY() - 3), {Installation_Date}, MONTH(@cell) = 7)))


    =COUNT(DISTINCT(COLLECT({Server}, {Installation_Date}, YEAR(@cell) = YEAR(TODAY()) - 3, {Installation_Date}, MONTH(@cell) = 7)))


    =COUNT(JOIN(DISTINCT(COLLECT({Server}, {Installation_Date}, YEAR(@cell) = YEAR(TODAY()) - 3, {Installation_Date}, MONTH(@cell) = 7)), ", "))

    When I do just a count of the distinct servers in my entire list, =COUNT(DISTINCT({Server})), it gives me the correct number of unique servers.

    Of course all but a couple of the servers have multiple dates other than the July 2018 date...not sure if this affects anything.

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    edited 07/07/21

    Hello @Brandis Smith

    Did you get your formula working? I wondered, for trouble shooting purposes, what this produces

    =COUNT(DISTINCT(COLLECT({Server}, {Installation_Date}, ISDATE(@cell)))

  • Hi @KDM - No, unfortunately, I did not get the formula to work, yet.

    The formula you provided gives me the total count of unique servers in my list (61).

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    @Brandis Smith

    Let's build on the formula, one term at a time.

    =COUNT(DISTINCT(COLLECT({Server}, {Installation_Date}, ISDATE(@cell), {Installation_Date}, MONTH(@cell) = 7))

  • Hi @KDM!! Thank you so much, I went ahead and added the Year portion to the formula and it worked!!

    =COUNT(DISTINCT(COLLECT({Server}, {Installation_Date}, ISDATE(@cell), {Installation_Date}, MONTH(@cell) = 7, {Installation_Date}, YEAR(@cell) = YEAR(TODAY()) - 3)))

    Thank you so much!! Now I will see if I can add some other conditions to ensure it remains automated as the years change!

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    Glad it worked. The working formula tells me that some of your 'dates' aren't dates- we excluded them from being examined with the condition of 'ISDATE'. You could add a temporary checkbox helper column to your source sheet with the formula IF(ISDATE([Installed On]@row), 0,1). This will add a checkmark to the 'non-dates'. You could then evaluate those rows to see why there is a date error.



  • @KDM, interestingly, I just did the check and all of them are dates...but, I would have never thought to use ISDATE.

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    hmm. That is interesting. I'm glad it worked!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!