google-sheetslooker-studio

How can I returns the number of unique values when date matters?

I have my dataset in Google Sheets and I wish to know the unique number of customers in a specific date range:

Let's say this is my data set

Customer                  Date
A                         22/07/2017
B                         24/07/2017
A                         23/07/2017

I use this formula COUNT_DISTINCT(Customer) however because row 3 have different date from row 1 the output is 3 (however, I wish to have 2 as my output)

Solution

  • If you want to know the number of unique values in column A regardless of the date then would this formula work:

    =COUNTUNIQUE(A2:A)

    Or is you want to combine this with a specific date range then:

    =COUNTUNIQUE(FILTER(A2:A,B2:B>=datefrom,B2:B<=dateto))

    In this formula, datefrom and dateto can be either a value or a cell reference