Sum if criteria in another table is matches criteria in sum table.

[BigShoes]

I am building a spreadsheet that splits out money between subcontractors and their equipment. I have a table where I store the equipment and the owners and the percentage of hour rate that each piece of equipment gets. Then on another sheet, I have it set up to put in the job information, money earned and so forth, and have a table that splits out the money that each piece of equipment earns based on hours worked and percentage of hourly rate. The last piece of the puzzle is where I can't get the logic to come together for me. Each subcontractor has several pieces of equipment and I need a total of the money that gets paid to each one. It needs to be dynamic since each job will have different subs working, but they will always be out of the same pool of subcontractors. How do I construct a formula that totals the amount each sub earned? I'm pasting a simplified version of my spreadsheet for your perusal.

 

[Snakehips]

@BigShoes Welcome.
Maybe just employ a 'Helper' column?
Shown here separated to column L but could be F or anywhere. Can be hidden if you wish.

MayMrXL.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1
2	Rig	Hours	Rate	Total	Nett			Rigs	Percentage	Owner		Helper
3	Tractor #1				5729			Tractor #1	100%	Company 1		Company 1
4	Tractor #2				28101			Tractor #2	100%	Company 1		Company 1
5	Tractor #3				10157			Tractor #3	100%	Company 1		Company 1
6	Rig #7				18249			Rig #4	100%	Company 2		Company 3
7	Rig #5				36754			Rig #5	100%	Company 2		Company 2
8	Marilyns Rig				6268			Rig #6	100%	Company 2		Company 4
9								Rig #7	100%	Company 3
10								Marilyns Rig	100%	Company 4
11								Panama Scraper	20%	Company 3
12								N's Rig	89%	Company 2
13								F's Bucket	11%	Company 1
14
15
16
17
18
19
20
21
22
23	Job Disbursment	Total
24	Company 1	43987
25	Company 2	36754
26	Company 3	18249
27	Company 4	6268
28
29
Sheet11

Cell Formulas
Range	Formula
F11:F13	F11	=IF(A11="","",XLOOKUP(A11,H$2:H$18,J$2:J$18,"!!",0))
L3:L13	L3	=IF(A3="","",XLOOKUP(A3,H$2:H$18,J$2:J$18,"!!",0))
B24:B29	B24	=IF(A24="","",SUMIF(L$3:L$13,A24,E$3:E$13))

Hope that helps.

 

[BigShoes]

Thanks for your help, Snakehips. Interestingly enough this is the solution I came up with myself after I had posted for help! I was hoping for a pure formulaic solution, but I just couldn't get anything to work and this is fine. I think I will take your suggestion and hide my helper columns just to keep everything tidy. Thanks again.