count dates

[LUIS ANGEL]

Hi Guys,

I have a column A with names and column B with dates.


I would like to have a formula that a can place in any other column
that will count how mnay unique dates are ther for a specific name.


For example if i have John Doe in A as one of the many names the
formula should tell me how many unique dates are there for John. With
in the many repeated dates that John may have, those dates should be
counted.


Example:


A B C
D E

DAYS
JOHN DOE 27-Sep JOHN DOE 3
JOHN DOE 27-Sep TINA DOE 3
TINA DOE 27-Sep CARL DOE 4
CARL DOE 27-Sep
CARL DOE 27-Sep
JOHN DOE 28-Aug
TINA DOE 29-Aug
TINA DOE 29-Aug
TINA DOE 29-Aug
CARL DOE 30-Aug
JOHN DOE 29-Aug
TINA DOE 30-Aug
CARL DOE 31-Aug
CARL DOE 25-Sep


Hope some one can help.... THNX

 

[AB]

There might be smarter way of doing this but for my 2 cents of worth,
try this:
insert a helper column (C, for instance) and havet his formula in
there:
=A2&B2 (fill it down as far as your data go)
(so all it does it concatenates NAMES with DATES).
So now what you need to know is how many unique values in that helper
column there are for a specific name.
Now, asuming that your
NAMES are in A2:A10
DATES are in B2:B0 (this one doesn't really matter that much as we'll
be using the helper column instead)
HELPER data is in C2:C10
The specfic name that you want to count dates for are in E2
try this formula:
=SUMPRODUCT(((A2:A10)=E2)/COUNTIF(C2:C10,C2:C10))

This should work. I couln't manage to figure out how to achieve the
same without the helper column...