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]

Excuse my mention of UNIQUE Dates. What I'm referring to are DISTINCT dates. My apologies!

 

[CA_Punit]

Post data in xl2bb

sample try
=countifs(D:D,">="&A2,D:D,"<="&B2,B:B,A6)

 

[DEMDEJ]

Post data in xl2bb

sample try
=countifs(D:D,">="&A2,D:D,"<="&B2,B:B,A6)

Thank you, but when I try orange, it gives me a count of 4. There's only 2 distinct dates for orange within the date range 6/1/20 - 6/8/2020. How can I get 2?

 

[CA_Punit]

Ok you need the specific date to be equal to 06/01/2020 and 08/01/2020

just delete the comparative operator i.e.

=countifs(D:D,A2,D:D,B2,B:B,A6)

 

[DEMDEJ]

Ok you need the specific date to be equal to 06/01/2020 and 08/01/2020

just delete the comparative operator i.e.

=countifs(D:D,A2,D:D,B2,B:B,A6)

Unfortunately, that does not work either

 

[Eric W]

How about:

Book1
	A	B	C	D	E	F	G
1						Start	End
2		Banana		6/1/2020		6/1/2020	6/8/2020
3		Banana		6/2/2020
4		Orange		6/1/2020
5		Orange		6/1/2020		Banana	2
6		Orange		6/1/2020		Orange	2
7		Orange		6/8/2020		Apple	4
8		Apple		6/1/2020		Grapes	0
9		Apple		6/2/2020
10		Apple		6/3/2020
11		Apple		6/4/2020
12		Grapes		7/1/2020
13		Grapes		7/2/2020
14		Grapes		7/3/2020
15		Grapes		7/4/2020
16		Grapes		7/5/2020
Sheet2

Cell Formulas
Range	Formula
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)))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[CA_Punit]

i am so sorry i did completely wrong. i ignored your requirement details.

Anyways @Eric W has come to our rescue.

 

[DEMDEJ]

How about:

Book1
	A	B	C	D	E	F	G
1						Start	End
2		Banana		6/1/2020		6/1/2020	6/8/2020
3		Banana		6/2/2020
4		Orange		6/1/2020
5		Orange		6/1/2020		Banana	2
6		Orange		6/1/2020		Orange	2
7		Orange		6/8/2020		Apple	4
8		Apple		6/1/2020		Grapes	0
9		Apple		6/2/2020
10		Apple		6/3/2020
11		Apple		6/4/2020
12		Grapes		7/1/2020
13		Grapes		7/2/2020
14		Grapes		7/3/2020
15		Grapes		7/4/2020
16		Grapes		7/5/2020
Sheet2

Cell Formulas
Range	Formula
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)))
Press CTRL+SHIFT+ENTER to enter array formulas.

This is absolutely perfect! Thank you Eric! You are a genius!

 

[Eric W]

Glad we could help.