SUMIF and COUNTIF across separate data files.

[Bdethloff]

I am having trouble joining two data sources for linear regression...

I am trying to combine tire maintenance data with daily production data from the truck on which the tire was found.

In Excel terms, I need to perform SUMIF and COUNTIF from two separate data sources. 

My first data source is grouped by the serial number of the tire, then by the truck on which the tire was recorded, I can also see the date range during which the tire was on the truck. It looks like this...

Serial Number	Truck	Min_Date	Max_Date
07215	033	2011-10-07	2011-12-09
07215	035	2012-06-14	2012-10-29
07215	036	2012-04-22	2012-05-02

My second data source is production data, where individual activities (dozens per day) are captured and organized by truck. It looks like this...

Truck	Date	Hours Driven	Operator ID
033	2011-10-17	.5	012
033	2011-10-17	.52	012
033	2012-06-20	.43	011

I need to simultaneously join these two files and Sum/Count the corresponding production data according to the recorded maintenance data. 

[sjm · Accepted solution]

@Bdethloff I may not be fully understanding what the output should look like, but a potential solution is attached. It seems that we can simply join the two tables using the Truck ID. And then use the Summarize tool to sum hours by truck, serial #, date, driver.

Not sure if you need any checks in place to make sure the production data falls within the dates ranges in the first table. The last record in the second table wasn't within the date ranges. Anyway, I built in a check.

[Bdethloff]

Very smart, thank you. 

[Bdethloff]

Sorry. One quick follow-up. The join isn't working for me because I have thousands of serial numbers assigned to the same truck. So, it would be more like this... I have two tyres (indicated by their serial numbers) who were on truck 33 at the same time... In need the production data from Truck 33 for every applicable date to be assigned to those two tyres. 

Truck	Date	Hours Driven	Driver ID
033	12/13/16	2	52
033	12/14/16	2	52
050	15/3/17

Serial	Truck	Start	Stop
07215	033	12/12/16	14/2/17
07215	050	15/3/17	10/10/17
07215	051	3/3/15	11/13/16
07744	033	12/12/16	14/2/17
07744	052	2/4/16	19/5/16
07744	050	1/4/17	12/12/17