By-line totals

[dmj120]

I am trying to create a summary table of expenses for a massive project for 35 people (each assigned a "type"). My data set is:

[TABLE="class: grid, width: 600"]
<tbody>[TR]
[TD]Type
[/TD]
[TD]Pay Rate
[/TD]
[TD]Days
[/TD]
[TD]Hotel
[/TD]
[TD]Per Diem
[/TD]
[TD]total
[/TD]
[/TR]
[TR]
[TD]1099
[/TD]
[TD]15.00
[/TD]
[TD]15
[/TD]
[TD]0
[/TD]
[TD]50
[/TD]
[TD]=((pay*8)*days)+hotel+per diem -- $1850
[/TD]
[/TR]
[TR]
[TD]1099
[/TD]
[TD]18.00
[/TD]
[TD]20
[/TD]
[TD]500
[/TD]
[TD]50
[/TD]
[TD]$3430
[/TD]
[/TR]
[TR]
[TD]Internal
[/TD]
[TD]35.00
[/TD]
[TD]10
[/TD]
[TD]200
[/TD]
[TD]50
[/TD]
[TD]$3050
[/TD]
[/TR]
[TR]
[TD]Vendor
[/TD]
[TD]100.00
[/TD]
[TD]10
[/TD]
[TD]0
[/TD]
[TD]50
[/TD]
[TD].....
[/TD]
[/TR]
</tbody>[/TABLE]


My summary table is:

[TABLE="class: grid, width: 600"]
<tbody>[TR]
[TD]Type
[/TD]
[TD]Pay
[/TD]
[TD]Hotel
[/TD]
[TD]Per Diem
[/TD]
[TD]Total
[/TD]
[/TR]
[TR]
[TD]1099
[/TD]
[TD]$5280
[/TD]
[TD]sumifs()
[/TD]
[TD]sumifs()
[/TD]
[TD]sum(b2:d2)
[/TD]
[/TR]
[TR]
[TD]Internal
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Vendor
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

The hotel and Per Diem are easily done with sumifs(). I'm running into an issue with the types because each type has to have the individual row calculated then totaled.

It would be something like sum( (b2*8)*c2), (b3*8)*c3), etc. ) but I don't want to hard code it - more are being added everyday, so I'd like to have a simple formula to find, calculate and add everything.

Thanks for any ideas.
Josh

 

[Fluff]

This is my take on the full set

Excel 2013/2016
	A	B	C	D	E	F
1	Type	Pay Rate	Days	Hotel	Per Diem	total
2	1099	15	15	0	50	1850
3	1099	18	20	500	50	3430
4	Internal	35	10	200	50	3050
5	Vendor	100	10	0	50	8050
6
7
8	Type	Pay	Hotel	Per Diem	Total
9	1099	4680	500	100	5280
10	Internal	2800	200	50	3050
11	Vendor	8000	0	50	8050
Sheet2

Cell Formulas
Range	Formula
F2	=B2*8*C2+D2+E2
B9	=SUMPRODUCT(--($A$2:$A$5=$A9),($B$2:$B$5)*($C$2:$C$5)*8)
C9	=SUMIF($A$2:$A$5,$A9,D$2:D$5)
D9	=SUMIF($A$2:$A$5,$A9,E$2:E$5)
E9	=SUM(B9:D9)

 

[jtakw]

Haven't been on for couple of weeks.

My take on the "Full set" in Post # 10 is based on OP's answer in Post # 7 for my question in Post # 4 (where "Per Diem" - by definition, is per day).