Counting unique dates in a specific period is a common requirement in data analysis. Whether you’re tracking project timelines, monitoring attendance, or analyzing sales trends, knowing how many unique days are active within a given time frame is crucial. In Google Sheets, the COUNTUNIQUEIFS function is the most straightforward way to do this, though several alternative formulas exist. This tutorial walks you through step-by-step methods to count unique dates in any date range.
Introduction
Imagine you have a list of transactions, but you only want to know how many days had activity in a month. Simply counting all dates will give you the wrong result because duplicates are included. Counting unique dates for a specific period ensures you get an accurate tally of active days.
Google Sheets offers several ways to do this — including COUNTUNIQUEIFS; QUERY with UNIQUE; DCOUNT with UNIQUE; FILTER with COUNTUNIQUE; and COUNTIFS with UNIQUE. We’ll explore each option in this tutorial.
The Problem with Counting Unique Dates in a Date Range
A basic COUNTUNIQUE formula counts unique dates across an entire column but does not allow filtering by a date range.
For example, consider a column with multiple repeated dates, spanning November 1–30. If you want to count only the unique dates between November 1–10, using COUNTUNIQUE alone will include dates outside your target range.
To get accurate counts, you need a formula that combines uniqueness with date-based conditions.
Using COUNTUNIQUEIFS to Count Unique Dates in a Date Range
Suppose your dates are in A3:A (with A2 as the header). To count unique dates between November 1, 2020 and November 10, 2020, use:
=COUNTUNIQUEIFS(
A3:A,
A3:A, ">="&DATE(2020,11,1),
A3:A, "<="&DATE(2020,11,10)
)Notes:
- The
DATE(year, month, day)function ensures proper date evaluation. - You can also use cell references instead of hardcoding dates:
COUNTUNIQUEIFS(
A3:A,
A3:A, ">="&C2,
A3:A, "<="&D2
)Where C2 and D2 contain your start and end dates.
Alternative Formula Options
1. Using UNIQUE with DCOUNT to Count Dates in a Date Range
DCOUNT can also count unique dates if your data has headers:
=DCOUNT(
UNIQUE(A2:A),
1,
{{"Date"; ">="&DATE(2020,11,1)}, {"Date"; "<="&DATE(2020,11,10)}}
)UNIQUE(A2:A)serves as the “database.”- The
1indicates the first column. - Criteria are defined in a small array.
Using cell references for criteria:
You can also keep your date criteria in cells instead of hardcoding them into the formula. However, with DCOUNT, the criteria cells must include the comparison operator along with the date, and the date format should match the format in your data column.
For example, if your dates are in column A with the header in A2, set up the criteria table in C1:D2 like this:
| Date | Date |
|---|---|
| >=1/11/2020 | <=10/11/2020 |
Then use:
=DCOUNT(
UNIQUE(A2:A),
1,
C1:D2
)
Where C1:D2 contains the start and end date criteria.
2. Using UNIQUE with QUERY to Count Dates in a Date Range
=QUERY(
UNIQUE(A3:A),
"SELECT COUNT(Col1) WHERE Col1 >= date '2020-11-01' AND Col1 <= date '2020-11-10' LABEL COUNT(Col1) ''"
)- Dates must follow the
yyyy-mm-ddformat. - To use cell references:
=QUERY(
UNIQUE(A3:A),
"SELECT COUNT(Col1) WHERE Col1 >= date '" & TEXT(C2,"yyyy-mm-dd") & "' AND Col1 <= date '" & TEXT(D2,"yyyy-mm-dd") & "' LABEL COUNT(Col1) ''"
)3. Using COUNTUNIQUE with FILTER to Count Dates in a Date Range
The FILTER function with COUNTUNIQUE is often more practical:
=COUNTUNIQUE(
FILTER(
A3:A,
A3:A >= DATE(2020,11,1),
A3:A <= DATE(2020,11,10)
)
)- Replace hardcoded dates with cell references (
C2andD2) as needed.
4. Using UNIQUE with COUNTIFS to Count Dates in a Date Range
COUNTIFS doesn’t inherently count unique values, but combining it with UNIQUE works:
=COUNTIFS(
UNIQUE(A3:A), ">="&DATE(2020,11,1),
UNIQUE(A3:A), "<="&DATE(2020,11,10)
)- Again, replace hardcoded dates with cell references for dynamic evaluation.
Summary
To count unique dates within a specific date range in Google Sheets:
- Use COUNTUNIQUEIFS for a simple, straightforward solution.
- For more advanced or flexible scenarios, DCOUNT with UNIQUE, QUERY with UNIQUE, FILTER with COUNTUNIQUE, and COUNTIFS with UNIQUE are viable alternatives.
- Using cell references for the start and end dates makes your formulas dynamic and easy to update.
By applying these techniques, you can accurately track active days, events, or transactions for any period in your dataset.
