countdistinct a column but its not working


I have this table and the formula for duration is:

the formula for the grouped and sorted is: 

when i used count distinct to count the instances of the multiple days(e.g. 1 day occurred 8 times, 2 days occurred 5 times) it wasn't displaying the correct count..


Help please :)




  • Avatar
    Janice Janczyn Official comment

    Hi Jazmine,

    COUNTDISTINCT will return a count of each unique item passed into it and GROUP will return one of each unique item. For details, https://support.klipfolio.com/hc/en-us/articles/215547998-GROUP-GROUPBY-and-COUNTDISTINCT.

    Given your table:

       GROUP( 1, 1, 1, 2, 2, 2, 2, 2 ) returns 1, 2

      COUNTDISTINCT( 1, 1, 1, 2, 2, 2, 2, 2 ) returns 3, 5

    Your table contains 8 rows, but GROUP & COUNTDISTINCT will return only 2 items. To align your data and have a count show up for each row, use the LOOKUP function (https://support.klipfolio.com/hc/en-us/articles/216182777-LOOKUP):

       LOOKUP( DurationDays, GROUP( DurationDays), COUNTDISTINCT( DurationDays) )



  • 0
    Omar Elmasry

    Thanks Janice.

    I've been trying to do this for the past 3 days

    You're the best Janice.



Please sign in to leave a comment.