Are you struggling to count unique dates in a date range in Google Sheets? If you thought COUNTUNIQUE was the answer, think again! In this article, we’ll explore five different formula options that will help you tackle this problem with ease. So, let’s dive in!
Date Range Sample and Criteria for the Test
Before we delve into the formulas, let’s set up the scenario for our test. Consider the following:
- The dates you want to count are in the range A3:A17.
- The criteria for the date range are in cells C2 and D2.
- You want to count the unique dates between 01/11/2020 and 10/11/2020.
- Note that all the dates in this example are formatted as DD/MM/YY.
The result of the unique count should be 6. These dates are highlighted in the image below for clarity.
Now that you have the context, let’s explore the five formula options for counting unique dates in Google Sheets.
Formula Option 1: Dcount to Count Unique Dates in a Date Range
The first formula option we’ll explore is based on the database function DCOUNT. Here’s how it works:
=dcount( unique(A2:A17), 1, {{"Date";">="&date(2020,11,1)},{"Date";"<="&date(2020,11,10)}} )
In this formula, unique(A2:A17) returns a unique date range. The number 1 represents the relative position of the date column in the database. We then apply the criteria using a criteria array.
But what if you want to use the criteria outside the formula? Simply follow these steps:
- Insert the field label “date” in cells C1 and D1, which match the field label in A2.
- Change the C2 date from 1/11/20 to
">=1/11/20"and D2 date from 10/11/20 to"<=10/11/20". - Use the adjusted formula
=dcount( unique(A2:A17), 1, G1:H2 ).
It’s as simple as that!
Formula Option 2: COUNTUNIQUEIFS to Count Unique Dates in a Date Range
The second formula option we recommend is COUNTUNIQUEIFS. Unlike COUNTUNIQUE, this function can handle criteria. Here’s how it looks:
=COUNTUNIQUEIFS( A3:A17, A3:A17, ">=date(2020,11,1)", A3:A17, "<=date(2020,11,10)" )
If you prefer using cell references for the criteria, use:
=COUNTUNIQUEIFS( A3:A17, A3:A17, ">=C2", A3:A17, "<=D2" )
To choose between DCOUNT and COUNTUNIQUEIFS, keep in mind that DCOUNT is ideal for date ranges that mimic a database structure, while COUNTUNIQUEIFS is suitable for general ranges.
Formula Options 3 and 4: Combo Formulas – Query and Filter
Next, we have two combo formulas – Query and Filter.
For the Query formula, use:
=query( unique(A3:A17), "Select Count(Col1) where Col1>=date '2020-11-1' and Col1<=date '2020-11-10' label Count(Col1)''" )
If you prefer using cell references for the criteria, use:
=query( unique(A3:A17), "Select Count(Col1) where Col1>='"&TEXT(C2,"yyyy-mm-dd")&"' and Col1<='"&TEXT(D2,"yyyy-mm-dd")&"' label Count(Col1)''" )
For the Filter combo formula, use:
=count( unique( filter( A3:A17, A3:A17>=date(2020,11,1), A3:A17<=date(2020,11,10) ) ) )
To use cell references instead of hardcoded criteria, replace >=date(2020,11,1) with >=C2 and <=date(2020,11,10) with <=D2.
Formula Option 5: Countifs
Last but not least, we have the Countifs formula.
=countifs( unique(A3:A17),">=date(2020,11,1)", unique(A3:A17), "<=date(2020,11,10)" )
To use cell references for the criteria, replace >=date(2020,11,1) with >=C2 and <=date(2020,11,10) with <=D2.
These are the five powerful formula options available to you for counting unique dates in a date range in Google Sheets. With these tips in your toolbox, you’ll be able to count unique dates in no time!
Thanks for reading, and if you want to explore more Google Sheets tips and tricks, visit Crawlan.com.
