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 :)
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

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,210
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,627
Messages
5,838,441
Members
430,548
Latest member
hh_dh2001

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top