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!
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,420
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Your welcome (you really should thank Aladin as well for the formula ;))
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

rizkadiana

New Member
Joined
Sep 5, 2020
Messages
18
Office Version
  1. 2016
Platform
  1. Windows
Your welcome (you really should thank Aladin as well for the formula ;))


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, sure! Thank you so much Aladin for the formula, it works and can be applied in another context but with similar condition!

You have helped me finish this part of my task, hope in the future, help will easily come to yours if needed!!!

Thank you!
 

Watch MrExcel Video

Forum statistics

Threads
1,112,798
Messages
5,542,569
Members
410,560
Latest member
1ndependent
Top