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

DEMDEJ

New Member
Joined
Jul 8, 2020
Messages
7
Office Version
  1. 2016
Platform
  1. Windows
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):

Sheet1.JPG


The calculations are on Sheet 2:

Sheet2.JPG


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
Joined
Jul 8, 2020
Messages
7
Office Version
  1. 2016
Platform
  1. Windows
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!
 

Some videos you may like

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
Joined
Aug 18, 2015
Messages
10,347
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
Joined
Jul 8, 2020
Messages
7
Office Version
  1. 2016
Platform
  1. Windows
That works perfectly! Thank you. I'd love to know when you write your article!

Have a great weekend!
 

CA_Punit

Well-known Member
Joined
Nov 18, 2019
Messages
882
Office Version
  1. 365
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,114,487
Messages
5,548,351
Members
410,828
Latest member
A9Bosv3
Top