Count the amount of consecutive dates

Will an employees consecutive dates always be listed consecutively, or might there be:

John 2016-03-05
Mike 2016-02-03
John 2016-03-06

If the employees' leave dates will always be grouped together as you've shown, then the formula you listed isn't too far off. Something like this in D2 (then filled down) should work:

=COUNTIF($A$2:$A$2010,$D2)-SUMPRODUCT(($A$2:$A$2010=$D2)*($B$3:$B$2011-$B$2:$B$2010=1))

With data laid out as presented this is another way. This array formula entered in D1 and filled down as in the below.

If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Formula:

Please Login or Register  to view this content.

	A	B	C	D
1	John	1/2/2016	John	3
2	John	1/3/2016	Mike	1
3			Keith	2
4	John	2/15/2016
5
6	John	3/5/2016
7	John	3/6/2016
8
9	Mike	2/3/2016
10	Mike	2/4/2016
11	Mike	2/5/2016
12
13	Keith	3/1/2016
14
15	Keith	4/10/2016