Count distinct values in a column if it falls within a date range

I am trying to run a formula in one sheet (let's call it sheet 1) to return a count of distinct values in an entire column of a different sheet (sheet 2) only if the corresponding date (also in sheet 2) falls within a date range.
Sheet 1: Column [Site Receiving Transfer] is the column I would like to count the distinct values from. Column [Date Shipped] contains the dates I want to check against the date range criteria.
Sheet 2: Column [Count of sites] is where the formula will go. Columns [Start Date] and [End date] are what I want to use as the beginning and ending of my date range for the date filter. I'd prefer to reference these cells so that I can easily update the beginning and ending of my date range later without updating the formula.
I know a countif(distinct formula is probably where I would start but can't figure out the rest.
FYI the actual names of the sheets are as follows:
Sheet 1: Outbound Shipments from Depot
Sheet 2: Sites Requesting Materials - YTD
Answers
-
Hi @levnichk
You can try this:
=COUNT(DISTINCT(COLLECT({Site Receiving Transfer}, {Date Shipped}, AND(@cell >= [Start Date]@row, @cell <= [End Date]@row))))
Melissa Yamada
melissa@insightfulsheets.com
Data made simple, spreadsheets reimagined -
That worked! I replaced
{Site Receiving Transfer}, {Date Shipped}
with the references to the other sheet ranges and that did it. Thank you.
-
Happy to help! 😊
Melissa Yamada
melissa@insightfulsheets.com
Data made simple, spreadsheets reimagined -
Great Insight
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.5K Get Help
- 433 Global Discussions
- 152 Industry Talk
- 494 Announcements
- 5.3K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 77 Community Job Board
- 506 Show & Tell
- 34 Member Spotlight
- 2 SmartStories
- 307 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!