# count unique dates based multiple criteria within given date range

#### aceif

##### New Member
I'm using the formula =ArrayFormula(SUM(--(FREQUENCY(IF(Data!A:A=D2,Data!B:B),Data!B:B)>0))) to count unique Dates in Column B if Column A matches the name in Column D however I'm struggling to refine the formula to only give me unique dates within the date range specified in F2 and G2

 Name Date Name No. of Shifts From Until Anthony Brown 01/05/2015 Peter Gray 4 01/05/2015 27/05/2015 Anthony Brown 01/05/2015 Tim Sherwood 17 Anthony Brown 01/05/2015 Sachin Patel 16 Anthony Brown 01/05/2015 Anthony Brown 19 Anthony Brown 01/05/2015 Anthony Brown 04/05/2015 Anthony Brown 04/05/2015 Anthony Brown 04/05/2015 Anthony Brown 04/05/2015 Anthony Brown 04/05/2015 Anthony Brown 04/05/2015 Anthony Brown 04/05/2015 Anthony Brown 04/05/2015 Anthony Brown 04/05/2015 Anthony Brown 04/05/2015 Anthony Brown 04/05/2015 Anthony Brown 05/05/2015 Anthony Brown 05/05/2015 Anthony Brown 05/05/2015 Anthony Brown 05/05/2015 Anthony Brown 05/05/2015 Anthony Brown 05/05/2015 Anthony Brown 05/05/2015 Anthony Brown 05/05/2015 Anthony Brown 05/05/2015 Anthony Brown 05/05/2015 Anthony Brown 05/05/2015 Anthony Brown 05/05/2015 Anthony Brown 05/05/2015 Anthony Brown 05/05/2015 Anthony Brown 05/05/2015 Anthony Brown 05/05/2015

<tbody>
</tbody>

### Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Hi,
In column A put a blank column called count.
assuming your names and dates are in B and C with individual names in E. Reults in F and Dates in G and H.

Type this formula in A2 and copy down =IF(OR(B2<>B1,C2<>C1),1,0).

In F2 type this formula =SUM(IF(\$B\$2:\$B\$60=\$E2,IF(\$C\$2:\$C\$60>=\$G\$2,IF(\$C\$2:\$C\$60<=\$H\$2,\$A\$2:\$A\$60)))).

Its an array formula that requires you to press Ctrl, Shift and Enter Keys all at the same time. Done correctly you will see { and } at the start and end of the formula.

I have only used 60 rows to test it.Adjust the formula as required.

Good luck.

What are you requiring - a unique count of dates per name related to the From-Until interval?

Last edited:
Perfect, that's done the trick. You're a genius!!

Replies
1
Views
396
Replies
9
Views
147
Replies
7
Views
282
Replies
5
Views
272
Replies
9
Views
548

1,196,360
Messages
6,014,811
Members
441,847
Latest member
hw407

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

### Which adblocker are you using?

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

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