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

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

Does the formula bar show { and } around the formula?

### Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

##### New Member
Does the formula bar show { and } around the formula?

No, it does not. How can I repair this?

#### MARK858

##### MrExcel MVP
That is probably not a repair needed. You probably haven't applied the Ctrl-Shift Enter correctly.

##### New Member
What language is your Excel in?

Is this what you meant by languange?

Do correct me if I'm wrong.

Thank you

#### MARK858

##### MrExcel MVP

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

What I want you to is clear C15.
Click C15
Paste the formula below directly into the formula bar (not the cell)
Excel Formula:
``=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))``
Select the whole formula including the = sign.
Then as Aladin said press and hold Ctrl and Shift and press Enter.
Has the formula now been surrounded by {} brackets?

##### New Member
Not before copying the formula.

What I want you to is clear C15.
Click C15
Paste the formula below directly into the formula bar (not the cell)
Excel Formula:
``=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))``
Select the whole formula including the = sign.
Then as Aladin said press and hold Ctrl and Shift and press Enter.
Has the formula now been surrounded by {} brackets?

YES IT WORKS! And it shows the result I expected!
Thank you very much Mark&Aladin, I really really appreciate your response, and assistance even after!
Hope you have a pleasant day, and days are running smoothly for you all!

I still do not understand though why is it happening?
If I were to apply the formula in different case, I should follow the exact same steps then, including the ctrl+shift+enter?

#### MARK858

##### MrExcel MVP

If you mean if you are putting the formula somewhere else then yes. If you mean for the cells below the formula then you can just drag the formula down.
The selecting the whole formula shouldn't be necessary, I just wanted to guarantee that you were still in the formula bar.

#### sandy666

##### Well-known Member
you can achieve this with Pivot Table (and Data Model)

 Ñama Fruit Distinct Count of Date Alexander Melon 1 Angelica Apple 3 Pear 1 Eliza Coconut 2 Peggy Durian 1 Melon 1

##### New Member
If you mean if you are putting the formula somewhere else then yes. If you mean for the cells below the formula then you can just drag the formula down.
The selecting the whole formula shouldn't be necessary, I just wanted to guarantee that you were still in the formula bar.

Okay Mark, I understand now. Thank you very much for your help!
Thank you for the patience and clear instructions.

I wish you all the best!

##### New Member
you can achieve this with Pivot Table (and Data Model)

 Ñama Fruit Distinct Count of Date Alexander Melon 1 Angelica Apple 3 Pear 1 Eliza Coconut 2 Peggy Durian 1 Melon 1

Yes,this also works, thank you for your suggestion!
Have a great day!

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