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

#### DEMDEJ

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

### Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

#### Eric W

##### MrExcel MVP
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

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

Have a great weekend!

#### CA_Punit

##### Well-known Member
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
ABCDEFGHIJ
1StartEnd
2Banana10/07/202010/07/202024/07/202010/07/2020
3Banana11/07/202024/07/2020
4Orange12/07/2020Eric FormulaPunit tried
5Banana12/07/2020Banana33
6Orange12/07/2020Orange22
7Orange13/07/2020Apple44
8Apple14/07/2020Grapes22
9Apple15/07/2020
10Apple10/07/2020
11Apple11/07/2020
12Grapes24/06/2020
13Grapes25/06/2020
14Grapes26/06/2020
15Grapes10/07/2020
16Grapes11/07/2020
17
Sheet1
Cell Formulas
RangeFormula
D4,D16,D13:D14,D11,D7:D9D4=D3+1
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)))
H5:H8H5=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))
D6D6=D4

Replies
22
Views
447
Replies
3
Views
32
Replies
1
Views
78
Replies
17
Views
560
Replies
3
Views
115