Counting Unique Dates within a Date Range With One Critieria (with Photos)

[DEMDEJ]

Hello All!

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.
The full dataset is on Sheet1 (for simplicity, I've only illustrated 16 rows, but it has potential to be within the thousands):

The calculations are on Sheet 2:

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.

Any assistance is greatly appreciated! Stay safe!

 

[DEMDEJ]

How would I use the IF statement to have values that return a "0" be blank with the formula that you provided?

=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)))

Once again, all help is greatly appreciated!

 

[Eric W]

I keep meaning to write an article about this, since the question comes up so often, but I haven't gotten to it. There are at least 4 ways, but they all have pros and cons. Try this first:

=IFERROR(1/(1/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)))),"")

still with CSE.

 

[DEMDEJ]

That works perfectly! Thank you. I'd love to know when you write your article!

Have a great weekend!

 

[CA_Punit]

I have also tried one after failing to solve it. I have tried to do in a different way but @Eric W formula has inspired me after failing miserably

S7_BuiltinFunctions_Start.xlsx
	A	B	C	D	E	F	G	H	I	J
1						Start	End
2		Banana		10/07/2020		10/07/2020	24/07/2020		10/07/2020
3		Banana		11/07/2020					24/07/2020
4		Orange		12/07/2020			Eric Formula	Punit tried
5		Banana		12/07/2020		Banana	3	3
6		Orange		12/07/2020		Orange	2	2
7		Orange		13/07/2020		Apple	4	4
8		Apple		14/07/2020		Grapes	2	2
9		Apple		15/07/2020
10		Apple		10/07/2020
11		Apple		11/07/2020
12		Grapes		24/06/2020
13		Grapes		25/06/2020
14		Grapes		26/06/2020
15		Grapes		10/07/2020
16		Grapes		11/07/2020
17
Sheet1

Cell Formulas
Range	Formula
D4,D16,D13:D14,D11,D7:D9	D4	=D3+1
G5:G8	G5	=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)))
H5:H8	H5	=SUM(IF(IFERROR(($D$2:$D$16/FREQUENCY((ROW(INDIRECT("A1:A"&$I$3-$I$2+1))),IFERROR(MATCH($D$2:$D$16,$I$2+(ROW(INDIRECT("A1:A"&$I$3-$I$2+1))-ROW($A$1)),0)*($B$2:$B$16=$F5),0))),0)>1,1,0))
D6	D6	=D4