Providing how many times staff members have been late by staff ID

OK

So i have a huge dataset showing staff clocking in and out times.

Column A: Staff ID
Column B: IN Time
Column C: Out Time
Column D: Lateness Time


Ok so bearing in mind this data set has 1000's of staff ID's and only some of the rows contain a lateness time (which is shown as a negative number, -10 means they were 10 minutes late).

I need a way of generating a table/list that is going to automatically count/say that this staff ID from column A appears to have a negative number in Column D this many times.

Obviously not every staff member has had lateness, so it would not be feasable to have EVERY staff member listed, only those who have a negative number in a column D.





Hope this is clear - and that someone can help!

Use column E as a helper, with a formula like this in E2:

=IF(D2<0,MAX(E$1:E1)+1,"-")

then copy down to the bottom of your data (the hyphens help to show you this). The formula will produce a unique sequential number for those records where there is a lateness value entered. Then in column G you can list the members of staff who have been late with this formula in G2:

=IFERROR(INDEX(A:A,MATCH(ROWS($1:1),E:E,0)),"")

and you can copy this down until you start to get blanks. In H2 you can have this formula:

=IF(G2="","",COUNTIFS(A:A,G2,D:D,"<0"))

and copy this down to give you a count of the number of times each person is late.

Hope this helps.

Pete