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!

 

[Aladin Akyurek]

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?

 

[rizkadiana]

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

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

 

[MARK858]

That is probably not a repair needed. You probably haven't applied the Ctrl-Shift Enter correctly.
You also haven't answered my question about what language your Excel is in.

 

[rizkadiana]

What language is your Excel in?

Is this what you meant by languange?

Do correct me if I'm wrong.
I am still learning about this. You can assist me to which menu should I check.

Thank you

 

[MARK858]

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)

=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?

 

[rizkadiana]

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)

=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]

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]

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

 

[rizkadiana]

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!

 

[rizkadiana]

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!