Count Unique Dates With Two Criteria (with Photos)

[rizkadiana]

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.

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

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!

 

[alansidman]

Using 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
	A	B	C
1	Name	Fruit	#Dates
2	Angelica	Apple	3
3	Angelica	Pear	1
4	Eliza	Coconut	2
5	Peggy	Melon	1
6	Peggy	Durian	1
7	Alexander	Melon	2
Sheet2

 

[MARK858]

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

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]

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]

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

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]

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
	A	B	C
1	Nama	Fruit	Date
2	Angelica	Apple	01/09/2020
3	Angelica	Pear	02/09/2020
4	Eliza	Coconut	01/09/2020
5	Eliza	Coconut	02/09/2020
6	Peggy	Melon	01/09/2020
7	Peggy	Durian	02/09/2020
8	Angelica	Apple	03/09/2020
9	Alexander	Melon	03/09/2020
10	Alexander	Melon	03/09/2020
11	Angelica	Apple	01/09/2020
12
13
14	Name	Fruit	Number of dates
15	Angelica	Apple	#VALUE!
16	Angelice	Pear	1
17	Eliza	Coconut	2
18	Peggy	Melon	1
19	Peggy	Durian	1
20	Alexander	Melon	1
21
Sheet1

Cell Formulas
Range	Formula
C15	C15	=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]

T

Using 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
	A	B	C
1	Name	Fruit	#Dates
2	Angelica	Apple	3
3	Angelica	Pear	1
4	Eliza	Coconut	2
5	Peggy	Melon	1
6	Peggy	Durian	1
7	Alexander	Melon	2
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]

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
	A	B	C
1	Nama	Fruit	Date
2	Angelica	Apple	01/09/2020
3	Angelica	Pear	02/09/2020
4	Eliza	Coconut	01/09/2020
5	Eliza	Coconut	02/09/2020
6	Peggy	Melon	01/09/2020
7	Peggy	Durian	02/09/2020
8	Angelica	Apple	03/09/2020
9	Alexander	Melon	03/09/2020
10	Alexander	Melon	03/09/2020
11	Angelica	Apple	01/09/2020
12
13
14	Name	Fruit	Number of dates
15	Angelica	Apple	#VALUE!
16	Angelice	Pear	1
17	Eliza	Coconut	2
18	Peggy	Melon	1
19	Peggy	Durian	1
20	Alexander	Melon	1
21
Sheet1

Cell Formulas
Range	Formula
C15	C15	=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]

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]

What language is your Excel in?