find time average from columns after countif finds the columns

[sbeuk]

Hi,

Driver Sheet Monday
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]a[/TD]
[TD="align: center"]b[/TD]
[TD="align: center"]c[/TD]
[TD="align: center"]d[/TD]
[TD="align: center"]e[/TD]
[TD="align: center"]f[/TD]
[TD="align: center"]g[/TD]
[TD="align: center"]h[/TD]
[TD="align: center"]i[/TD]
[TD="align: center"]j[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]12344[/TD]
[TD="align: center"]76544[/TD]
[TD="align: center"]46443[/TD]
[TD="align: center"]76542[/TD]
[TD="align: center"]12567[/TD]
[TD="align: center"]98534[/TD]
[TD="align: center"]12578[/TD]
[TD="align: center"]27533[/TD]
[TD="align: center"]85621[/TD]
[TD="align: center"]06345[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]ab[/TD]
[TD="align: center"]cb[/TD]
[TD="align: center"]ab[/TD]
[TD="align: center"]cb[/TD]
[TD="align: center"]hi[/TD]
[TD="align: center"]jk[/TD]
[TD="align: center"]ab[/TD]
[TD="align: center"]no[/TD]
[TD="align: center"]cb[/TD]
[TD="align: center"]rs[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]16:35[/TD]
[TD="align: center"]16:15[/TD]
[TD="align: center"]17:01[/TD]
[TD="align: center"]16:50[/TD]
[TD="align: center"]18:00[/TD]
[TD="align: center"]17:50[/TD]
[TD="align: center"]17:35[/TD]
[TD="align: center"]18:05[/TD]
[TD="align: center"]17:30[/TD]
[TD="align: center"]18:07[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]17:00[/TD]
[TD="align: center"]16:45[/TD]
[TD="align: center"]17:30[/TD]
[TD="align: center"]17:25[/TD]
[TD="align: center"]18:35[/TD]
[TD="align: center"]18:30[/TD]
[TD="align: center"]18:00[/TD]
[TD="align: center"]18:40[/TD]
[TD="align: center"]18:00[/TD]
[TD]18:45[/TD]
[/TR]
</tbody>[/TABLE]

Driver Summary, separate sheet)
[TABLE="width: 500"]
<tbody>[TR]
[TD]driver: AB[/TD]
[TD]Number cars/day[/TD]
[TD]average time/day (mins)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]mon[/TD]
[TD]5[/TD]
[TD]32:23[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]tue[/TD]
[TD]6[/TD]
[TD]21:12[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]wed[/TD]
[TD]3[/TD]
[TD]45:23[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]thu[/TD]
[TD]8[/TD]
[TD]30:12[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]fri[/TD]
[TD]1[/TD]
[TD]34:12[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I have drivers (initials, row 2) out each day, in various vehicles (vin Numbers, row 1), and clock their in and out times - rows 4 and 5.
I would like to create a summary for each driver that details how many times a day they've been out, and their overall average time on the road over a week.
I managed to use CountIf to look across row 2 of the top sheet for the different initials which allowed me to summarise how many vehicles each driver had taken out, and could use the data from one drivers' column to get how many minutes he was out, then I was stumped as to how to ask the Sheet to look at the times from all the occurrences of any given drivers' initials to sum up the times!

I hope this has made some sense, and please forgive me for any daft questions or lack of understanding. I assure you that any shortcomings will be mine.

Thanks in advance.

Regards,

sbe

 

[thisoldman]

I would use a SUMPRODUCT formula. Let's assume the rows end in column J.

=SUMPRODUCT((A2:J2 = Driver) * (A5:J5 - A4:J4))
​

Driver would be a cell that contains the driver's ID.

(A2:J2 = Driver) – an array of TRUE and FALSE values

(A5:J5 - A4:J4) – The second array: end times minus start times

* – Multiplication coerces TRUE and FALSE to the integers 1 and 0. Each value in the first array gets multiplied by the corresponding value in the second array. The result of these multiplications will also be an array.

SUMPRODUCT() – Calculates the sum of the array created by the multiplication
​

Hope this helps.

 

[sbeuk]

Hi,

Thank you for your reply.

I put your formula, thus:

=SUMPRODUCT((monday!B10:AG10 = "ab") * (monday!B13:AG13 - monday!B12:AG12)) - for monday, then
=SUMPRODUCT((tuesday!B10:AG10 = "ab") * (tuesday!B13:AG13 - tuesday!B12:AG12)) - into tuesday and so on for that persons' week, but I just got "#VALUE!" error?

I'm sure my error will cause mirth, but this is rather new to me, so please excuse my ignorance.

Kind regards,

sbe

 

[thisoldman]

Hi,

Thank you for your reply.

I put your formula, thus:

=SUMPRODUCT((monday!B10:AG10 = "ab") * (monday!B13:AG13 - monday!B12:AG12)) - for monday, then
=SUMPRODUCT((tuesday!B10:AG10 = "ab") * (tuesday!B13:AG13 - tuesday!B12:AG12)) - into tuesday and so on for that persons' week, but I just got "#VALUE!" error?

I'm sure my error will cause mirth, but this is rather new to me, so please excuse my ignorance.

I'm not cracking a smile, I hate when I have to chase error results from a formula.

The most likely cause of the #VALUE! error is a cell, or several cells, in monday!B12:AG13 that cannot be interpreted as a number: cells containing text.

This next formula may work. It's an array formula, and must be entered using Ctrl+Shift+Enter, instead of a plain Enter. If done correctly, curly braces, '{' and '}', will enclose the formula.

=SUMPRODUCT((monday!B10:AG10 = "ab") * (IFERROR(monday!B13:AG13 - monday!B12:AG12, 0)))
​

The SUMPRODUCT function is designed to work with arrays. IFERROR is not designed to do so and must be forced to be an array formula with Ctrl+Shift+Enter. If the curly braces do not appear, the results will still be a number but it will probably not be a correct number.

 

[sbeuk]

Oh my goodness! That's it!
It produced a decimal number, wondered what had happened, then realised I may need to format the cells as time, and it appears to be working with my dummy data
Huge thanks, I will let you know how it works on the 'live' sheet'
Incredibly grateful for your help.
Respect.
Best regards,
sbe

 

[sbeuk]

As promised - update - works beautifully with the live data. Thank you.

I've been asked to add a feature: When a driver goes out, sometimes their vehicle has a problem, this is highlighted by changing the relevant cell colour A, B, C etc to red. How could I ask the sheet to look at the A, B, C.... row, and if it is red, put the VIN number (row 1) in a table on a new sheet?
I changed the SUMPRODUCT formula to look at the relevant rows, but got stumped when I realised it had to check a cell colour, rather than a value.

Thanks in advance.

Best regards,

Sbe

 

[thisoldman]

As promised - update - works beautifully with the live data. Thank you.

You're welcome.

For your next question, there is no builtin Excel function to determine a cell's fill color. I'm no more than a novice at VBA. Let us hope some other forum user will offer a solution.