Search code examples
exceluniquecriteriacountifsumproduct

Excel - Counting Distinct Dates within a Date Range With One Critieria

I'm trying to create a formula that counts the number of unique dates within a date range based on the type of item.

I've tried several different formulas and researched ways to do it, but each formula was so specific to the poster's specific problem, that it did not work so I decided to post mine for your help.

This formula counts duplicated days when I only want distinct days counted between two dates:

=COUNTIFS(Sheet1!$D:$D,">="&$A$2,Sheet1!$D:$D,"<="&$B$2,Sheet1!$B:$B,A8)

The full dataset is on Sheet1 (for simplicity, I've only illustrated 16 rows, but it has potential to be within the thousands):

sheet2

The calculations are on Sheet 2:

enter image description here

In short, using the data on Sheet1, I want to know how many unique dates occurred between, say, 6/1/2020 and 6/8/2020 with the criteria being a banana. These items will change in the future so I'd like the date and items to be referenced cells.

Solution

  • Thank you!

    I decided to use the array formula below: =SUM(SIGN(FREQUENCY(IF($B$2:$B$16=F5,IF($D$2:$D$16>=$F$2,IF($D$2:$D$16<=$G$2,$D$2:$D$16))),$D$2:$D$16)))

    Worked like a charm!