Count consecutive dates by person

I am putting something together for HR regarding sickness occurrences.

The data is exported into sheet 2 as:

A = Task (sickness)
B = Payroll Number
C = Date
so there are multiple entries per person for each date.


I've tried to put together a formula to count consecutive days sickness per payroll.

On sheet 1 - I will have a list of every employee in the company in column A
In Column B i would like the highest total consecutive days sickness they taken from sheet2

I have attached a basic sheet of what im looking for and working with.
Many thanks in advance.

Try this array formula

=MAX(FREQUENCY(IF(Sheet2!$B$2:$B$17=$A2,IF(Sheet2!$D$3:$D$18-Sheet2!$D$2:$D$17=1,ROW(Sheet2!$D$2:$D$17))),IF((Sheet2!$B$2:$B$17<>$A2)+(Sheet2!$D$3:$D$18-Sheet2!$D$2:$D$17<>1),ROW(Sheet2!$D$2:$D$17))))+(COUNTIF(Sheet2!$B$2:$B$17,$A2)>0)

Confirm with ctrl+shift+enter

Windy

This is fantastic thank you!
Done the trick.

Glad I could help

Windy