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

 

[James Ravenswood]

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

With your data in A2 thru B15, In C2 enter:

=A2 & B2 and copy down

In D2 enter 1
In D3 enter:

=IF(COUNTIF($C$2:C3,C3)>1,0,1) and copy down

We see:

JOHN DOE 27-Sep JOHN DOE40448 1
JOHN DOE 27-Sep JOHN DOE40448 0
TINA DOE 27-Sep TINA DOE40448 1
CARL DOE 27-Sep CARL DOE40448 1
CARL DOE 27-Sep CARL DOE40448 0
JOHN DOE 28-Aug JOHN DOE40418 1
TINA DOE 29-Aug TINA DOE40419 1
TINA DOE 29-Aug TINA DOE40419 0
TINA DOE 29-Aug TINA DOE40419 0
CARL DOE 30-Aug CARL DOE40420 1
JOHN DOE 29-Aug JOHN DOE40419 1
TINA DOE 30-Aug TINA DOE40420 1
CARL DOE 31-Aug CARL DOE40421 1
CARL DOE 25-Sep CARL DOE40446 1

The zeros in column D correspond to duplicated data. Now the formulas
are simple:

=SUMPRODUCT(--(A2:A15="JOHN DOE"),--(D215))
=SUMPRODUCT(--(A2:A15="TINA DOE"),--(D215))
=SUMPRODUCT(--(A2:A15="CARL DOE"),--(D215))

to get the desired result.

 

[Pete_UK]

The zeros in column D correspond to duplicated data.  Now the formulas
are simple:

=SUMPRODUCT(--(A2:A15="JOHN DOE"),--(D215))
=SUMPRODUCT(--(A2:A15="TINA DOE"),--(D215))
=SUMPRODUCT(--(A2:A15="CARL DOE"),--(D215))

to get the desired result

Even simpler, after your earlier formulae:

=SUMIF(A2:A15,"John Doe",D215)
=SUMIF(A2:A15,"Tina Doe",D215)
=SUMIF(A2:A15,"Carl Doe",D215)

The ranges could be full columns, even with older versions of Excel.

Hope this helps.

Pete

 

[James Ravenswood]

Even simpler, after your earlier formulae:

=SUMIF(A2:A15,"John Doe",D215)
=SUMIF(A2:A15,"Tina Doe",D215)
=SUMIF(A2:A15,"Carl Doe",D215)

The ranges could be full columns, even with older versions of Excel.

Hope this helps.

Pete

Thanks!

 

[Ron Rosenfeld]

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

To do this with no helper columns:

=SUM(N(FREQUENCY((Name=D2)*Date,(Name=D2)*Date)>0),
SUMPRODUCT(-OR(((Name=D2)*Date)=0)))

where Name and Date are your ranges in Columns A & B containing the
data. Blanks are acceptable, but there can be no text in the "Date"
column.

 

[Ron Rosenfeld]

To do this with no helper columns:

=SUM(N(FREQUENCY((Name=D2)*Date,(Name=D2)*Date)>0),
SUMPRODUCT(-OR(((Name=D2)*Date)=0)))

where Name and Date are your ranges in Columns A & B containing the
data. Blanks are acceptable, but there can be no text in the "Date"
column.

I did not point out that D2 represents a cell where the name of
interest is located. e.g. JOHN DOE

And also that the routine is NOT case sensitive.

 

[Anand]

Hi,

You can also use a pivot table to do this. Select the data, apply
pivot table(Data menu > Pivot Table for 2003 and older versions and
Insert ribbon Pivot table for 2001). Put Names as column lables date
as row lables or vice versa and put name in sum area. Ensure that
value feild setting is set to count and you get the result table. In
case you make any changes to your data set the pivot table needs to be
refreshed. In case you add more data to your list the Pivot table
range would need updation to include all the rows. Besides all columns
should have a name to create a pivot table.

Hope that helps,
Warm regards,
Anand Kumar