Results 1 to 10 of 10
Thread: count unique dates

20110428, 16:11 #1
 Join Date
 Jan 2001
 Location
 La Jolla, CA
 Posts
 1,593
 Thanks
 44
 Thanked 77 Times in 72 Posts
count unique dates
I have a column with dates (A col) various people (B col) did tasks.
Sometimes a person does a task multiple times  could be twice in 1 day and once on three different other days. I want to be able to count that this person did the task FOUR times (four unique dates).
I don't want to use a filter or pivot table. Is there a formula that could calculate this for each person found in the B column?
I would have a list of the unique people on another sheet and want the corresponding count next to their name.
Thanks in advance.

20110428, 17:28 #2
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Could you attach a sample file showing your setup?
Steve

20110428, 17:38 #3
 Join Date
 Jan 2001
 Location
 La Jolla, CA
 Posts
 1,593
 Thanks
 44
 Thanked 77 Times in 72 Posts
sample.
Here's a sample.

20110429, 02:11 #4
 Join Date
 Dec 2009
 Location
 Earth
 Posts
 9,105
 Thanks
 64
 Thanked 1,125 Times in 1,048 Posts
I can't see how you can do it as a formula, but a macro would work. You then use a formula to call the macro and return the result, or run the macro and place the result in the currently selected cell.
cheers, Paul

20110429, 09:26 #5
 Join Date
 Jan 2001
 Location
 La Jolla, CA
 Posts
 1,593
 Thanks
 44
 Thanked 77 Times in 72 Posts
For the life of me, I couldn't come up with a formula either so ended up using a pivot table and referenced the results as needed. Phew.
Thanks for viewing this.

20110504, 12:09 #6
 Join Date
 Jan 2001
 Location
 Redcliff, Alberta, Canada
 Posts
 4,066
 Thanks
 2
 Thanked 5 Times in 5 Posts
Try this
Here is a whopper of an array formula that does what you want
{=SUM(IF(FREQUENCY(
MATCH((TRANSPOSE(IF((($A$2:A$14&B$2:B$14)=($A$2:$A $14&D2)),($A$2:A$14&B$2:B$14),0))),
(TRANSPOSE(IF((($A$2:A$14&B$2:B$14)=($A$2:$A$14&D2 )),($A$2:A$14&B$2:B$14),0))),0),
MATCH((TRANSPOSE(IF((($A$2:A$14&B$2:B$14)=($A$2:$A $14&D2)),($A$2:A$14&B$2:B$14),0))),
(TRANSPOSE(IF((($A$2:A$14&B$2:B$14)=($A$2:$A$14&D2 )),($A$2:A$14&B$2:B$14),0))),0))
>0,1))1}
I've broken it up into multiple lines for easier reading[b]Catharine Richardson (WebGenii)
WebGenii Home Page
Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

20110504, 16:57 #7
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
If you can stand a intermediate column (may be hidden), in C2 enter:
=1/SUMPRODUCT(($B$2:$B$14=B2)*($A$2:$A$14=A2))
Copy from C2 and paste to C3:C14
Then in E2:
=SUMIF($B$2:$B$14,D2,$C$2:$C$14)
Copy from E2 to E3:E6
Steve

20110505, 10:23 #8
 Join Date
 Jan 2001
 Location
 Redcliff, Alberta, Canada
 Posts
 4,066
 Thanks
 2
 Thanked 5 Times in 5 Posts
Steve
I was hoping you'd look at my honkin' formula and tell me  oh, this is how you can reduce it to two lines. I'm always slightly uneasy when I produce such a gigantic formula, as it often means I've chosen an unnecessarily complicated solution.
Although in this case ...[b]Catharine Richardson (WebGenii)
WebGenii Home Page
Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

20110509, 07:07 #9
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,353
 Thanks
 4
 Thanked 229 Times in 210 Posts
=COUNT(1/FREQUENCY(IF($B$2:$B$14=D2,IF($A$2:$A$14<>"",$A$2: $A$14)),IF($B$2:$B$14=D2,IF($A$2:$A$14<>"",$A$2:$A $14))))
arrayentered, should do it.Regards,
Rory
Microsoft MVP  Excel

20110509, 10:56 #10
 Join Date
 Jan 2001
 Location
 Redcliff, Alberta, Canada
 Posts
 4,066
 Thanks
 2
 Thanked 5 Times in 5 Posts
I knew it!
[b]Catharine Richardson (WebGenii)
WebGenii Home Page
Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile