Count the amount of consecutive dates

[zuprex]

Hi,
This is my first thread, and I have been looking around on this forum for an answer but haven't really found one that suits my needs.

I have a list of employees and the dates they have been on sick leave. I would need a formula that calculates the occurrences of consecutive dates to be able to distinguish how many sick periods the employee has had. Column A = Names of employees. Column B = dates they have been sick.

John 2016-01-02
John 2016-01-03

John 2016-02-15

John 2016-03-05
John 2016-03-06

Mike 2016-02-03
Mike 2016-02-04
Mike 2016-02-05

Keith 2016-03-01

Keith 2016-04-10

In this case I want excel to output (in Column C and D respectively)
John 3
Mike 1
Keith 2.

I found a close solution in this thread: http://www.excelforum.com/showthread.php?t=1075879, but I didn't get it to work in my excel sheet.

I have also tried this formula: =SUMPRODUCT((A2:A2010=D2)*(B3:B2011-B2:B2010=1))+(COUNTIF(A2:A2010;D2)>0)
Which didn't do the trick at all (don't really know how that one is working at all since I get inconstancies in the output).

Hopefully some kind soul out there can help me!

Thanks in advance,

[CAntosh]

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

[FlameRetired]

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.

=SUM(IF(FREQUENCY(IF(COUNTIF(C1,$A$1:$A$15)=1,ROW($A$1:$A$15)),IF(COUNTIF(C1,$A$1:$A$15) <> 1,ROW($A$1:$A$15))),1))

	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