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!
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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?
 

Some videos you may like

Excel Facts

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

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,420
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
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

New Member
Joined
Sep 5, 2020
Messages
18
Office Version
  1. 2016
Platform
  1. Windows
What language is your Excel in?

Is this what you meant by languange?

1599349768134.png


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

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,420
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile

ADVERTISEMENT

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?
 

rizkadiana

New Member
Joined
Sep 5, 2020
Messages
18
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
Nov 12, 2010
Messages
13,420
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile

ADVERTISEMENT

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
Joined
Oct 24, 2015
Messages
7,056
you can achieve this with Pivot Table (and Data Model)

ptdm.png


ÑamaFruitDistinct Count of Date
AlexanderMelon1
AngelicaApple3
Pear1
ElizaCoconut2
PeggyDurian1
Melon1
 

rizkadiana

New Member
Joined
Sep 5, 2020
Messages
18
Office Version
  1. 2016
Platform
  1. Windows
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

New Member
Joined
Sep 5, 2020
Messages
18
Office Version
  1. 2016
Platform
  1. Windows
you can achieve this with Pivot Table (and Data Model)

ÑamaFruitDistinct Count of Date
AlexanderMelon1
AngelicaApple3
Pear1
ElizaCoconut2
PeggyDurian1
Melon1

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

Watch MrExcel Video

Forum statistics

Threads
1,112,796
Messages
5,542,549
Members
410,560
Latest member
1ndependent
Top