Count of Unique Values based on 2 criteria

[asc6429@gmail.com]

Hi All! I've been a lurker for a while, but am totally stumped on something I need to do in Excel, and I can't seem to find anything that I can "morph" into my solution.


Here is what I'm trying to do:

COUNT the number of days a specific educator held classes.

Problem: there are records by PATIENT -- so one educator holding a class on 10/5/12 that had 4 patients attend will show 4 instances of a class on 10/5/12... and I only want to count that once.


Here is my data:

Column:

AH: (Educator Name, concatenated name field of "general")

Z: Date of Cass (date field)


I need to count the unique dates per educator name and return that number.

Example:

8/30/12 Educator A Count of Classes: 2

8/30/12 Educator A

8/30/12 Educator B Count of Classes: 2

8/16/12 Educator A

8/9/12 Educator B


I'd prefer not to have to sort the data beforehand, as it is the raw data dump and could affect other things. I don't have a problem doing it in a few steps (a few columns).


I hope that I explained it well enough, if you have any other questions please let me know.


I look forward to seeing your genius responses!

 

[sdsurzh]

Hi,


Can you please try the below formula.


C1=COUNTIFS(A:A,A1,B:B,B1) and drag


Thanks,

Suresh Kumar S

 

[Luke M]

Check out this thread:

http://chandoo.org/forums/topic/formula-to-count-unique-values-with-criteria


If we think of the problem in reverse, you want to count the unique instances of DATES which meet a specific criteria (Educator Name). I have a feeling your array formula will look like this:

=SUM(IF(FREQUENCY(IF(AH1:AH7="Educator A",MATCH(Z1:Z7,Z1:Z7,0)),ROW(AH1:AH7)-ROW(AH1)+1),1))

 

[sdsurzh]

Hi,


You can also try the below formula. Assume your data on A column and EducatorName on B column paste the formula in C column


=SUM(IF((A:A=A1)*(B:B=B1),1,0)) (use control+shift+enter)


Thanks,

Suresh Kumar S

 

[SirJB7]

Hi, asc6429@gmail.com!


May I suggest you to change your nick name? It's too long and it overlaps the comment area making it unreadable. If you want to keep your email address you can put it in the website field of your profile, then when clicking on you nick on any comment the user will be led to your email.


Regards!

 

[Haseeb A]

Hello,


Since you have a valid dates field, you could also use with CTRL+SHIFT+ENTER


=SUM(IF(FREQUENCY(IF(PatientCol="Patient Name",DateField),DateField),1))


replace named ranges with your ranges.


Regards,

Haseeb