1. ## 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.

2. Could you attach a sample file showing your setup?

Steve

3. ## sample.

Here's a sample.

4. 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

5. 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.

6. ## 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

7. 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

8. 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 ...

9. =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))))
array-entered, should do it.

10. I knew it!

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•