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

 

[SpillerBD]

This would be a good demonstration for PowerPivot and the difference between SUM and SUMX.
SUMX is like using a helper field to do your per line calculations. It changes the evaluation order. I think that in your Table, the column "Total" is not a good label as it will conflict with the desired label in your Pivot Table.

Do you have O365 or Office 2013 Professional Plus?

 

[dmj120]

Do you have O365 or Office 2013 Professional Plus?

No, I'm using 2010

 

[jtakw]

Hi,

I'm a bit confused with your description, and your SUMMARY "Pay" value of $5280 seems incorrect.
And for the Total, Doesn't "Per Diem" need to be Multiplied by Days Also ?

 

[Fluff]

Not sure I fully understand what you're after, but how about

Excel 2013/2016
	A	B	C	D	E
1	Type	Pay Rate	Days	Hotel	Per Diem
2	1099	15	15	0	50
3	1099	18	20	500	50
4	Internal	35	10	200	50
5	Vendor	100	10	0	50
6
7
8
9	Type	Pay	Hotel	Per Diem	Total
10	1099	4680	sumifs()	sumifs()	sum(b2:d2)
11	Internal
12	Vendor
Index

Cell Formulas
Range	Formula
B10	=SUMPRODUCT(--($A$2:$A$5=$A10),(B2:B5)*(C2:C5)*8)

 

[SpillerBD]

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

You should also covert your range to a Table. Then your column formula will extend as the Table grows and the the Pivot Table will just need a refresh to see the new rows without redefining the source range.

 

[dmj120]

Yes, the per diem would be (and is) multiplied by the number of days on my spreadsheet. I was just typing out a simple version of it.

Yes you are correct, I added the totals on the SUMMARY table; whoops.
the pay summary is:
=($15*8hrs)*15 days = 1800
=($15*8hrs)*20 days = 2880
so the summary pay should be $4680.

 

[dmj120]

Not sure I fully understand what you're after, but how about

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B10[/TH]
[TD="align: left"]=SUMPRODUCT(--($A$2:$A$5=$A10),(B2:B5)*(C2:C5)*8)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

That did the trick!! Thanks!!!!!! ray: ray:

 

[Fluff]

Glad it worked & thanks for the feedback

 

[jtakw]

Yes, the per diem would be (and is) multiplied by the number of days on my spreadsheet. I was just typing out a simple version of it.

Yes you are correct, I added the totals on the SUMMARY table; whoops.
the pay summary is:
=($15*8hrs)*15 days = 1800
=($15*8hrs)*20 days = 2880
so the summary pay should be $4680.

Just to give you the complete set:

Book1
	A	B	C	D	E	F
1	Type	Pay Rate	Days	Hotel	Per Diem	total
2	1099	15	15	0	50	2550
3	1099	18	20	500	50	4380
4	Internal	35	10	200	50	3500
5	Vendor	100	10	0	50	8500
6
7
8	Type	Pay	Hotel	Per Diem	Total
9	1099	$4,680	500	1750	$6,930
10	Internal	$2,800	200	500	$3,500
11	Vendor	$8,000	0	500	$8,500
Sheet368

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

All formulas copied down.