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...
On Sep 30, 10:54*pm, LUIS ANGEL <upslavazq...@gmail.com> wrote:
> 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
|
Similar Threads
