Counting unique values that fit two critera

[Keysto]

Hi all,

I'm pretty new to Excel and I'm having some trouble with a formula I want to use. I have 3 columns: the first is a list policy numbers (some are repeated where there were multiple things to correct on a policy, some are unique where only one thing was corrected), the second is the name of the employee who worked on the policy, the third is the date the policy was worked on. I've used COUNTIFS(B2:B100, "Name", C2:C100, "Date") to count the number of corrections for each person on each day (where Name is employees name and Date is the date I'm wanting the data for.

I'm stumped on how to get Excel to count the number of unique values in column A that match the criteria in my COUNTIFS statement. I would like to know how many unique policies each employee worked on for each date, not how many individual corrections they made.

I hope this makes sense! Thanks for any help

 

[Luke M]

This will work best if your Name and Date criteria are in a cell somewhere, and you use a cell reference. But, for the formula, you'll want to use this:
=SUM(IF(FREQUENCY(IF($B$2:$B$14<>"",IF(($B$2:$B$14=Name)*($C$2:$C$14=Date),MATCH("~"&$A$2:$A$14&"",$A$2:$A$14&"",))),ROW($B$2:$B$14)-ROW($A$2)+1),1))
Adjust the array sizes as needed. Confirm formula as an array with Ctrl+Shift+Enter, not just Enter.

Credit to @vijaySharma with this post:
http://chandoo.org/forum/threads/combine-countifs-and-unique-function.2871/#post-13744

 

[Keysto]

Thank you so much! Worked like a charm and was WAYYY more complicated than what I was imagining, I'd never have figured that out without help. I took your advise on the cell reference also and you're right, that will work better Thanks again!

 

[SirJB7]

Hi, Keysto!

Even if Luke M provided a fast solution (as he uses to do), as a new user you might want to (I'd say should and must) read this:
http://chandoo.org/forum/forums/new-users-please-start-here.14/

Regards!