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

levnichk

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

Melissa Yamada

Hi @levnichk

You can try this:

=COUNT(DISTINCT(COLLECT({Site Receiving Transfer}, {Date Shipped}, AND(@cell >= [Start Date]@row, @cell <= [End Date]@row))))

levnichk

That worked! I replaced

{Site Receiving Transfer}, {Date Shipped}

with the references to the other sheet ranges and that did it. Thank you.

Melissa Yamada

Happy to help! 😊

Humashankar

Great Insight