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

DEMDEJ

New Member
Joined
Jul 8, 2020
Messages
7
Office Version
2016
Platform
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!
 

Some videos you may like

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
Joined
Jul 8, 2020
Messages
7
Office Version
2016
Platform
Windows
Excuse my mention of UNIQUE Dates. What I'm referring to are DISTINCT dates. My apologies!
 

CA_Punit

Well-known Member
Joined
Nov 18, 2019
Messages
866
Office Version
365
Platform
Windows
Post data in xl2bb

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

DEMDEJ

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

CA_Punit

Well-known Member
Joined
Nov 18, 2019
Messages
866
Office Version
365
Platform
Windows

ADVERTISEMENT

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
Joined
Jul 8, 2020
Messages
7
Office Version
2016
Platform
Windows
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
Joined
Aug 18, 2015
Messages
10,087

ADVERTISEMENT

How about:

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
Joined
Nov 18, 2019
Messages
866
Office Version
365
Platform
Windows
i am so sorry i did completely wrong. i ignored your requirement details.

Anyways @Eric W has come to our rescue.
 

DEMDEJ

New Member
Joined
Jul 8, 2020
Messages
7
Office Version
2016
Platform
Windows
How about:

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!
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
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...
Top