Counting Unique Values In One Column Based On Values In Another Column

[Wookiee]

I've been wracking my brain for a few hours now trying to come up with a solution to this problem, so I've broken down and decided to ask my friends at PHD for some advice.


I have a report that I compile daily (I pull it from a different report with about 12 times more data than I need). There are 2 columns from which I need to get data. One column has a list of auditor names and the other column contains the dates for the various audits they have worked.


I need to come up with a formula, macro, or User-Defined Function* which can tell me how many different unique values (dates) appear in one column for each name in the auditor list.


I created a simple file with dummy data to illustrate what I'm hoping to identify:


https://docs.google.com/open?id=0B03QVPPcP2Uwd3dYaWI0eTdHaFk


Now the names are going to be pretty much constant. The important factor here is being able to count how many different unique dates show up in the second column for each name in the first.


Help me, Pointy-Haired Dilbert; you're my only hope!


Thank you.


*I'm certainly not averse to using macros, since I will be incorporating the formula or UDF into a macro eventually, either way

 

[Haseeb A]

Hello Wookiee,


For the dates in F8 with CTRL+SHIFT+ENTER,


=SUM(SIGN(FREQUENCY(IF(A$2:A$13=E8,B$2:B$13),B$2:B$13)))


then copy down.


For the items, in F8 with just ENTER.


=SUMPRODUCT((A$2:A$13=E9)*(B$2:B$13<>"")*(MATCH(A$2:A$13&"|"&B$2:B$13,A$2:A$13&"|"&B$2:B$13,0)=ROW(A$2:A$13)-ROW(A$2)+1))


then copy down.

 

[Wookiee]

Thank you so much, Haseeb! Those formulas certainly did the trick. I had originally thought a SUMPRODUCT formula might be the way to go, but I figured I was looking for a COUNT function of some sort. Looks like I need to dig deeper into Chandoo's Formula Forensics to better familiarize myself with the power of SUMPRODUCT.


I look forward to getting to work with that knowledge tomorrow and putting those formulas to good use.