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

#### DEMDEJ

##### New Member
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!

### Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

#### DEMDEJ

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

#### CA_Punit

##### Well-known Member
Post data in xl2bb

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

#### DEMDEJ

##### New Member
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?

#### Attachments

• 27.5 KB Views: 1

#### CA_Punit

##### Well-known Member

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

##### New Member
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

##### MrExcel MVP

Book1
ABCDEFG
1StartEnd
2Banana6/1/20206/1/20206/8/2020
3Banana6/2/2020
4Orange6/1/2020
5Orange6/1/2020Banana2
6Orange6/1/2020Orange2
7Orange6/8/2020Apple4
8Apple6/1/2020Grapes0
9Apple6/2/2020
10Apple6/3/2020
11Apple6/4/2020
12Grapes7/1/2020
13Grapes7/2/2020
14Grapes7/3/2020
15Grapes7/4/2020
16Grapes7/5/2020
Sheet2
Cell Formulas
RangeFormula
G5:G8G5=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

##### Well-known Member
i am so sorry i did completely wrong. i ignored your requirement details.

Anyways @Eric W has come to our rescue.

#### DEMDEJ

##### New Member

Book1
ABCDEFG
1StartEnd
2Banana6/1/20206/1/20206/8/2020
3Banana6/2/2020
4Orange6/1/2020
5Orange6/1/2020Banana2
6Orange6/1/2020Orange2
7Orange6/8/2020Apple4
8Apple6/1/2020Grapes0
9Apple6/2/2020
10Apple6/3/2020
11Apple6/4/2020
12Grapes7/1/2020
13Grapes7/2/2020
14Grapes7/3/2020
15Grapes7/4/2020
16Grapes7/5/2020
Sheet2
Cell Formulas
RangeFormula
G5:G8G5=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

##### MrExcel MVP
Glad we could help.

1,106,169
Messages
5,509,565
Members
408,743
Latest member
1245585

### This Week's Hot Topics

• Turn fraction around
Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
• TIme Clock record reformatting to ???
Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
• TextBox Match
hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
• Using Large function based on Multiple Criteria
Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
• Can you check my code please
Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
• Combining 2 pivot tables into 1 chart
Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...