# Count Unique Dates With Two Criteria (with Photos)

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

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

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

##### New Member

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.

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

##### New Member

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

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

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.

##### New Member
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
What language is your Excel in?

Replies
13
Views
439
Replies
4
Views
86
Replies
6
Views
130
Replies
3
Views
90
Replies
17
Views
537