Sumifs or nested ands

[RkGen]

Good morning:
I work for a trucking company and have several years worth of load & payroll data.

The data is contained in 2 tables.
Table 1 columns are: date, truck, driver, load, miles
Table 2 columns are: week start, week end, driver, cost.

Each driver can do multiple loads per day and what I'm trying to do is create a way to figure a labor cost per truck either by load or by day. The end result is to combine the data with 3 other tables in a power pivot controlled by a timeline to get a cost per day per truck. the 3 other tables are typical trucking expenses and are formatted: date, truck, driver, expense.

I've tried a couple variations of sumifs as well as multiple ands but either get an error or return value of zero.

Any assistance would be greatly appreciated.

 

[SpillerBD]

PowerPivot ... SumIFS...??
Make sure you have your relationship created and CALCULATE !
Its all about the Measures.

YouTube channels from Mike Girvin "ExcelIsFun" and Bill Jelen "MrExcel".
Mike for the long and thorough, Bill for the Quick.
Matt Allington, Rob Collie and Marco Russo(w Alberto Ferrari) have several good books on squeezing out the advantages of PowerPivot and DAX.

 

[RkGen]

I've watched a few of the videos which I though would relate.
Power pivots & relationships between tables is relatively new to me however, I have it working as it relates to tables that represent pure truck info (fuel, Tolls, Repairs etc).
The issue that I'm running into is that drivers move between trucks and their payroll costs vary from week to week so I'm having difficulty relating back to the truck to arrive at a cost per truck.
My initial thought was to calculate a rate per mile for each driver based on the specific week and multiply it for each load. From there, it's relatively straight forward. The problem is devising a formula to do that. That's where the sumifs or nested ands came in.
I'm not sure my approach is even feasible but I'll keep plugging away and will figure it out eventually. I was hoping someone could point me in the right direction and save me some time and a lot of frustration.
Thanks

 

[RkGen]

I've gone through many tutorials on sumifs, have worked the formula multiple times, multiple ways and keep coming up with zero values. There obviously something wrong with my logic or my thought process.

Below are examples of the 2 tables.

[TABLE="width: 1273"]
<colgroup><col><col><col span="12"><col><col span="3"></colgroup><tbody>[TR]
[TD="colspan: 2"]Labor Table[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Pay Date[/TD]
[TD]Home Department Code[/TD]
[TD]Driver[/TD]
[TD]Regular Pay Rate Amount[/TD]
[TD]Total Hours[/TD]
[TD]Total Gross[/TD]
[TD]FICA[/TD]
[TD]Health ins[/TD]
[TD]401(k) Employer[/TD]
[TD]Trip[/TD]
[TD]Telephone[/TD]
[TD]HSA[/TD]
[TD]Health Insurance[/TD]
[TD]W/C[/TD]
[TD]Total Cost[/TD]
[TD]Date[/TD]
[TD]End Date[/TD]
[TD]Miles[/TD]
[/TR]
[TR]
[TD]09/14/2018[/TD]
[TD]2[/TD]
[TD]Driver 1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]9/2/2018[/TD]
[TD]9/8/2018[/TD]
[TD]0.00[/TD]
[/TR]
[TR]
[TD]09/14/2018[/TD]
[TD]2[/TD]
[TD]Driver 2[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]9/2/2018[/TD]
[TD]9/8/2018[/TD]
[TD]0.00[/TD]
[/TR]
[TR]
[TD]09/14/2018[/TD]
[TD]2[/TD]
[TD]Driver 3[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]9/2/2018[/TD]
[TD]9/8/2018[/TD]
[TD]0.00[/TD]
[/TR]
[TR]
[TD]09/14/2018[/TD]
[TD]2[/TD]
[TD]Driver 4[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]9/2/2018[/TD]
[TD]9/8/2018[/TD]
[TD]0.00[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 1001"]
<colgroup><col><col><col span="12"></colgroup><tbody>[TR]
[TD="colspan: 2"]Detail Table[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Truck[/TD]
[TD]Driver[/TD]
[TD]Customer[/TD]
[TD]Date[/TD]
[TD]Trip#[/TD]
[TD]Load#[/TD]
[TD]Originatio[/TD]
[TD]n[/TD]
[TD]Destinatio[/TD]
[TD]n2[/TD]
[TD]Empty[/TD]
[TD]Loaded[/TD]
[TD]Total[/TD]
[TD]Revenue[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Driver 1[/TD]
[TD] [/TD]
[TD="align: right"]9/7/2018[/TD]
[TD] [/TD]
[TD="align: right"]218322[/TD]
[TD] [/TD]
[TD] OH[/TD]
[TD] [/TD]
[TD] CT[/TD]
[TD="align: right"]97.7[/TD]
[TD="align: right"]745.1[/TD]
[TD="align: right"]842.8[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Driver 2[/TD]
[TD] [/TD]
[TD="align: right"]9/7/2018[/TD]
[TD] [/TD]
[TD="align: right"]218310[/TD]
[TD] [/TD]
[TD] WV[/TD]
[TD] [/TD]
[TD] MA[/TD]
[TD="align: right"]279.5[/TD]
[TD="align: right"]502.4[/TD]
[TD="align: right"]781.9[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Driver 3[/TD]
[TD] [/TD]
[TD="align: right"]9/7/2018[/TD]
[TD] [/TD]
[TD="align: right"]218193[/TD]
[TD] [/TD]
[TD] ME[/TD]
[TD] [/TD]
[TD] GA[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1276.5[/TD]
[TD="align: right"]1276.5[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Driver 4[/TD]
[TD] [/TD]
[TD="align: right"]9/7/2018[/TD]
[TD] [/TD]
[TD="align: right"]218265[/TD]
[TD] [/TD]
[TD] KY[/TD]
[TD] [/TD]
[TD] ME[/TD]
[TD="align: right"]82.2[/TD]
[TD="align: right"]1156.5[/TD]
[TD="align: right"]1238.7[/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

and here is the formula: =SUMIFS(Detail[Total],Detail[Driver],Labor[@Driver],Detail[Date],">=Labor[@[Date]]",Detail[Date],"<=Labor[@[End Date]]")

I'm trying to calculate how many miles a driver has run in any particular week to use in another formula elsewhere in the spreadsheet.

Date & End date on the labor table are actually formulas based on the paydate. Not sure if it matters but figured I'd mention it.

If someone could point out where the issue resides, it would be greatly appreciated.

Thanks