Google Sheets – How to Get Unique Number of Days in Datetime Column

I was able to create two options:

Formula 1

=COUNTUNIQUE(FILTER(A2:A11,E2:E11<>""))

or

=COUNTA(UNIQUE(FILTER(A2:A11,E2:E11<>"")))

Formula 2

=COUNTA(QUERY(A2:E11, "SELECT COUNT(E) WHERE E<>'' GROUP BY A label COUNT(E) '' "))

Explained

The first two formulas are identical. They filter column A, where column E has entries. Afterwhich column A is filtered for unique names. These names are than counted. The COUNTUNIQUE function combines the COUNTA and UNIQUE.

The second formula will count the number of occurrences per unique business name having an entry (<> '' and group by). The result is counted. Remove the COUNTA function to see the intermediate result.

Example

See you're own example file for the results !!

I found a way to do it:

1. Select Column A and "Insert 1 left" to add a column. Your table data is moved over one to columns B - D.
2. In your newly blank Cell A2, enter formula
   =IF(COUNTIF($B$2:$B2,B2)=1, "Unique", "Duplicate")
3. Copy the formula from A1 and paste it down the A column range for as far as you have data. The "$" absolute references will ensure everything works smoothly.
4. Add a new sheet. Copy and paste your old sheet's B - D column headers to the new sheet's A - C.
5. In the your new sheet's cell A2, enter formula
   =FILTER(OldSheet!B2:D5, OldSheet!A2:A5 = "Unique")
6. Your new sheet now has your old sheets data -- Deduplicated!

I use it with ImportRange to separate Data Entry from Data Processing, so ImportData shouldn't present a problem.

You can hide the A column on your old sheet if you like.

Best of all, everything updates live on edit. So you can continue adding data to OldSheet and NewSheet will be always be current with filtered rows.