Count Unique dates for XYZ

jveyron

New Member
Joined
Mar 31, 2011
Messages
1
Hi all,
I am trying to achieve something a little bit beyond my level of knowledge on Excel. If I manage to do this though It will help improve my work.

Scenario:
In Sheet 1 I have a column of names, which may appear multiple times. For each appearance of a given name, there is a date/time associated with it in another column. This date may be unique, it may not.

Example:
15/03/2011 08:55 Name1
15/03/2011 07:40 Name1
14/03/2011 07:30 Name1
13/03/2011 07:20 Name2
12/03/2011 07:20 Name2

In Sheet 2, I have this column of names again but with duplicates removed. In the column next to it, I require the number of times the given name appears with unique DATES (Time is irrelevant but cannot be removed as this is how my reports are run, this needs to be taken into account)

Result:
Based on the above, Sheet 2 should appear like this:

Name 1 2
Name 2 2

As this is searching for criteria within a range in another sheet to determine which dates to count, I would assume some sort of array formula containing a vlookup will be needed but I havn't a clue where to begin.

Any assistance would be greatly appreciated!

Regards,
Jay Price :)
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

T. Valko

Well-known Member
Joined
May 9, 2009
Messages
16,623
Hi all,
I am trying to achieve something a little bit beyond my level of knowledge on Excel. If I manage to do this though It will help improve my work.

Scenario:
In Sheet 1 I have a column of names, which may appear multiple times. For each appearance of a given name, there is a date/time associated with it in another column. This date may be unique, it may not.

Example:
15/03/2011 08:55 Name1
15/03/2011 07:40 Name1
14/03/2011 07:30 Name1
13/03/2011 07:20 Name2
12/03/2011 07:20 Name2

In Sheet 2, I have this column of names again but with duplicates removed. In the column next to it, I require the number of times the given name appears with unique DATES (Time is irrelevant but cannot be removed as this is how my reports are run, this needs to be taken into account)

Result:
Based on the above, Sheet 2 should appear like this:

Name 1 2
Name 2 2

As this is searching for criteria within a range in another sheet to determine which dates to count, I would assume some sort of array formula containing a vlookup will be needed but I havn't a clue where to begin.

Any assistance would be greatly appreciated!

Regards,
Jay Price :)
Assuming the date/time is a true Excel date/time (numeric value).

Data in the range A2:B6.

D2 = Name1

Enter this array formula** in E2:

=SUM(IF(FREQUENCY(IF(B$2:B$6=D2,INT(A$2:A$6)),INT(A$2:A$6)),1))

** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,192
Hi all,
I am trying to achieve something a little bit beyond my level of knowledge on Excel. If I manage to do this though It will help improve my work.

Scenario:
In Sheet 1 I have a column of names, which may appear multiple times. For each appearance of a given name, there is a date/time associated with it in another column. This date may be unique, it may not.

Example:
15/03/2011 08:55 Name1
15/03/2011 07:40 Name1
14/03/2011 07:30 Name1
13/03/2011 07:20 Name2
12/03/2011 07:20 Name2

In Sheet 2, I have this column of names again but with duplicates removed. In the column next to it, I require the number of times the given name appears with unique DATES (Time is irrelevant but cannot be removed as this is how my reports are run, this needs to be taken into account)

Result:
Based on the above, Sheet 2 should appear like this:

Name 1 2
Name 2 2

As this is searching for criteria within a range in another sheet to determine which dates to count, I would assume some sort of array formula containing a vlookup will be needed but I havn't a clue where to begin.

Any assistance would be greatly appreciated!

Regards,
Jay Price :)
E2, control+shift+enter, not just enter:
Code:
=SUM(IF(FREQUENCY(IF($A$2:$A$6<>"",IF($B$2:$B$6=D2,
   MATCH(INT($A$2:$A$6),INT($A$2:$A$6),0))),
    ROW($A$2:$A$6)-ROW($A$2)+1),1))

where D2 has a name of interest like Name 1 and A2:B6 houses the sample you posted.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,461
Messages
5,528,939
Members
409,848
Latest member
Blomsten
Top