Count Unique Dates With Two Criteria (with Photos)

rizkadiana

New Member
Joined
Sep 5, 2020
Messages
18
Office Version
  1. 2016
Platform
  1. Windows
Hello All!

I'm trying to create a formula that counts the number of unique dates with two criteria.
I've actually searched within this forum and found a similar question, however because it was case-specific and the conditions differ to mine, the same cannot be applied to mine.
The one with 2 criteria, I've read but because there are no example, I cannot understand the logic, so it is hard to try applying to mine.

This is the picture for context.
The goal is that I want to know how many days, Angelica has eaten the fruit Apple.

unique dates 2 criteria.JPG


Angelica is mentioned 4 times in the dataset. However, the fruit apple is only 3 of them. In addition, the dates has duplicates = meaning the actual times Angelica has eaten Apple is 2 days.
The formula I expected is to show it like this

1599304357208.png


If tried using =SUM(--(FREQUENCY ) ; but this only have 1 criteria.

Really appreciated it if anyone can help me with this.
Thank you for all your assistance!
Hope you have a great day!
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
5,918
Office Version
  1. 2019
Platform
  1. Windows
Using Power Query

Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Fruit", type text}, {"Date", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Name", "Fruit"}, {{"#Dates", each Table.RowCount(_), Int64.Type}})
in
    #"Grouped Rows"

Book16
ABC
1NameFruit#Dates
2AngelicaApple3
3AngelicaPear1
4ElizaCoconut2
5PeggyMelon1
6PeggyDurian1
7AlexanderMelon2
Sheet2
 
Last edited:

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,420
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
the dates has duplicates = meaning the actual times Angelica has eaten Apple is 2 days.
How is it 2 days? She ate an Apple on 3 separate days, the first, second and third :unsure:

P.S. it would be handy if you could post your data with the boards XL2BB addin so we can copy/paste the data rather than retype it.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
In C15 control+shift+enter, not just enter, and copy down:

=SUM(IF(FREQUENCY(IF($C$2:$C$11<>"",IF($A$2:$A$11=A15,IF($B$2:$B$11=B15,MATCH($C$2:$C$11,$C$2:$C$11,0)))),ROW($C$2:$C$11)-ROW(INDEX($C$2:$C$11,1,1))+1),1))

By the way, Angelica is eating Apple on 3 different dates, not 2, in your sample.
 

rizkadiana

New Member
Joined
Sep 5, 2020
Messages
18
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

How is it 2 days? She ate an Apple on 3 separate days, the first, second and third :unsure:

P.S. it would be handy if you could post your data with the boards XL2BB addin so we can copy/paste the data rather than retype it.
My bad! I entered a bad example. And noted for the XL2BB, thank you for the input! I will be sure to use that in the future.
 

rizkadiana

New Member
Joined
Sep 5, 2020
Messages
18
Office Version
  1. 2016
Platform
  1. Windows
In C15 control+shift+enter, not just enter, and copy down:

=SUM(IF(FREQUENCY(IF($C$2:$C$11<>"",IF($A$2:$A$11=A15,IF($B$2:$B$11=B15,MATCH($C$2:$C$11,$C$2:$C$11,0)))),ROW($C$2:$C$11)-ROW(INDEX($C$2:$C$11,1,1))+1),1))

By the way, Angelica is eating Apple on 3 different dates, not 2, in your sample.
Yes about the sample my bad, the last part supposed to be in the same date as the first one!

Ok I have tried copying the formula in my table but it is error.
For info in my excel the comma (",") is stated as ";" = I've already changed it accordingly.


Latihan excel count distinct dates.xlsx
ABC
1NamaFruitDate
2AngelicaApple01/09/2020
3AngelicaPear02/09/2020
4ElizaCoconut01/09/2020
5ElizaCoconut02/09/2020
6PeggyMelon01/09/2020
7PeggyDurian02/09/2020
8AngelicaApple03/09/2020
9AlexanderMelon03/09/2020
10AlexanderMelon03/09/2020
11AngelicaApple01/09/2020
12
13
14NameFruitNumber of dates
15AngelicaApple#VALUE!
16AngelicePear1
17ElizaCoconut2
18PeggyMelon1
19PeggyDurian1
20AlexanderMelon1
21
Sheet1
Cell Formulas
RangeFormula
C15C15=SUM(IF(FREQUENCY(IF($C$2:$C$11<>"",IF($A$2:$A$11=A15,IF($B$2:$B$11=B15,MATCH($C$2:$C$11,$C$2:$C$11,0)))),ROW($C$2:$C$11)-ROW(INDEX($C$2:$C$11,1,1))+1),1))


Thank you for your replies, please advise on this
 

rizkadiana

New Member
Joined
Sep 5, 2020
Messages
18
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

T
Using Power Query

Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Fruit", type text}, {"Date", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Name", "Fruit"}, {{"#Dates", each Table.RowCount(_), Int64.Type}})
in
    #"Grouped Rows"

Book16
ABC
1NameFruit#Dates
2AngelicaApple3
3AngelicaPear1
4ElizaCoconut2
5PeggyMelon1
6PeggyDurian1
7AlexanderMelon2
Sheet2

Thank you for your reply! I actually still unfamiliar with PowerQuery, I supposed I will look into that and do some research before trying it on my dataset.

Thank you
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
Yes about the sample my bad, the last part supposed to be in the same date as the first one!

Ok I have tried copying the formula in my table but it is error.
For info in my excel the comma (",") is stated as ";" = I've already changed it accordingly.


Latihan excel count distinct dates.xlsx
ABC
1NamaFruitDate
2AngelicaApple01/09/2020
3AngelicaPear02/09/2020
4ElizaCoconut01/09/2020
5ElizaCoconut02/09/2020
6PeggyMelon01/09/2020
7PeggyDurian02/09/2020
8AngelicaApple03/09/2020
9AlexanderMelon03/09/2020
10AlexanderMelon03/09/2020
11AngelicaApple01/09/2020
12
13
14NameFruitNumber of dates
15AngelicaApple#VALUE!
16AngelicePear1
17ElizaCoconut2
18PeggyMelon1
19PeggyDurian1
20AlexanderMelon1
21
Sheet1
Cell Formulas
RangeFormula
C15C15=SUM(IF(FREQUENCY(IF($C$2:$C$11<>"",IF($A$2:$A$11=A15,IF($B$2:$B$11=B15,MATCH($C$2:$C$11,$C$2:$C$11,0)))),ROW($C$2:$C$11)-ROW(INDEX($C$2:$C$11,1,1))+1),1))


Thank you for your replies, please advise on this

Control+shift+enter, no just enter means:
Press down the control and the shift keys at the same while you hit the enter key. İf done properly, Excel will put a pair of { and } around the formula in recognition. Once done, you can copy down the formula cell for the other cındition pairs.
 

rizkadiana

New Member
Joined
Sep 5, 2020
Messages
18
Office Version
  1. 2016
Platform
  1. Windows
Control+shift+enter, no just enter means:
Press down the control and the shift keys at the same while you hit the enter key. İf done properly, Excel will put a pair of { and } around the formula in recognition. Once done, you can copy down the formula cell for the other cındition pairs.

Yes, I have followed your instruction and tap CTRL+SHIFT+ENTER before copying the formula you have posted.

It is still showing result as #VALUE!

Is it possible that the version I used, cannot follow the same function as yours?
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,420
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
What language is your Excel in?
 

Watch MrExcel Video

Forum statistics

Threads
1,112,802
Messages
5,542,585
Members
410,561
Latest member
Sasha Lawrence
Top