Totalling revenue each days for each vehicle where the revenue is in multiple rows.

Attached is a workbook with two sheets.

Sheet1 is some revenue and KPI data and Sheet2 is payroll data.

I have created the columns with yellow headings.

I've used a multi-conditional v-lookup formula to get the driver hours from Sheet2 using the JobDate and OperID. I've also omitted the rows i don't need by creating a formula that returns a YES or NO in the TP Movement Column. I only need the ones that say YES but i don't mind it the NO's are still there. The next column is the total revenue for the for that movement (Quantity x Rate).

What i want it to do is sum the total revenue column for each FleetNum on each day (JobDate). For example i have manually totalled PM678 for 02/11/2015 in column AN13 but i want this to do it automatically.

I hope this makes sense.


Thanks of your help!!!!

Hi bebo021999,

Thanks for the reply.

It doesn't seem to be working properly when i drag it down? It works for the initial vehicle and day but then it doesn't sum correctly and doesn't pick up every vehicle/day.

Hi FlameRetired,

Thanks for the reply! Is there a way of adding another criteria? I just noticed some FleetNum are used twice in a day by two different OperID. Can it been summed dependant on all three? JobDate, FleetNum & OperID?