Lateness Tracker

[Literae]

Hey all,

Thanks for your help with my previous problem on my Lateness Tracker, however I am expanding it. I have now included a new column which tracks the amount of Lateness Periods for a report. I need help with the formula for column D to count the amount of cells in this row (will copy formula down for the rest of the column) that are above 00:00:00 in the Lateness headed Columns. In the image below, I would want this to display 1 for example as there was only 1 day this week this person was late. F5 - O5 is the row range, but F5 - AV5 is the whole row, whichever is easier to work with.

For Total Late (Column C) I currently have a formula of: =SUMIF($F$3:$AV$3,"LATENESS",F5:AV5) which sums up the total lateness across the Row (F3 - AV3 is my range if this helps). I tried something similar to COUNTIF, but it was unsuccessful. If there is a shorter formula similar to this for the whole row, that would be great. However if the only way to do it is to calculate each week at a time, then add up the total of each week across the month, I can work with that too.

Lateness.PNG

Many thanks in advance!

Edit: I currently have: =SUM(5-COUNTIF(F5:O5,"00:00:00")) which is long winded, but something I can work around.
Edit: I now have: =SUM(20-COUNTIF(F5:AV5,"00:00:00")) with 20 being the amount of working days.

[cubangt]

This should work for column D if you are wanting to count ALL instances within that row
=COUNTIFS(F5:AV5,">00:00:00")

Column C sound like if it found say 3 instances of anything greater than 00:00:00 it would take those 3 values and SUM them for a total in that column correct.
I mean technically, you can just have a blanket sum across the whole row, since everything else will be 00:00:00, it will only sum the ones with values..

[Literae]

Hi Cubangt,

Sorry for the confusion, my Col C formula works fine. What I meant was that I thought I could try a similar formula for Col D. I was mentioning that as a reference if anyone could work out a similar formula as it was pretty much doing the same, but summing the Cells instead of counting them.

However I tried your =COUNTIFS(F5:AV5,">00:00:00") and that gave me "16.00" in the first row, but there is only 1 occurrence of lateness (above 00:00:00) so not sure what went wrong there. I currently have:

Attachment 732230

This is using the formula: =SUM(20-COUNTIF(F5:AV5,"00:00:00")) with 20 being the amount of working days. I just had to change the range and working days for each tab/month which was a long way round, but arrived at the solution I needed.

Thanks for your input anyway.

[Farley945]

Hi Literae,

you could have the following formula,

Please Login or Register  to view this content.

That will look for all amounts in the Lateness columns and without the $ after the letter in the second half of the formula if you have multiple lines if you drag the formula down it will only pick up the line you're on.

Hope that helps

[Literae]

Hi Farley945,

Thats perfect. Thats the one I was looking for. I thought it would be similar to my =SUMIF($F$3:$AV$3,"LATENESS",F5:AV5) in Col C to sum up the total time late, but couldnt get it to work for the Countif version. I think I was getting the ">00:00:00" bit wrong.

Thanks all for your help!