Count Date Once, Ignore Duplicates

Michelle Maas

I am pulling data from a time report. I want to identify how many days (through the Date column), someone (individual sheet) was in the Office (one of the allocations in the Location column drop-down list). Each day may have several rows related to one date, as there is a variety of allocations under the Department and Activity columns that can apply to the tasks that were carried out on any given day. I am only interested in the Date and Location, and only want to count each day once within a date period, say 1 Jan to 5 Apr.

Paul Newcome

You would use a COUNT/DISTINCT/COLLECT combo for this.

=COUNT(DISTINCT(COLLECT({Date Column}, {Date Column}, AND(@cell >= DATE(2025, 01, 01), @cell <= DATE(2025, 04, 05)), "Location Column}, @cell = "Location")))

You use the COLLECT to gather all of the dates within the date column that are within the specified date range and have a specific location. You then wrap it in a DISTINCT function to filter out the duplicates from the results, and then finally the COUNT function to output the count of distinct values that were collected.