stuartmacdonald
New Member
- Joined
- May 26, 2009
- Messages
- 48
I have a formula I'm trying to simplify to calculate how work is spread among project staff.
I need to calculate a value (£) based on a value in the same row (%), so if H4 = 20%, return 20% of C4, in cell N4 - simple
=SUM($H4*C4,$H5*C5,$H6*C6,$H7*C7)
[TABLE="class: grid, width: 958"]
<colgroup><col><col><col span="3"><col><col span="4"><col><col><col span="4"></colgroup><tbody>[TR]
[TD][/TD]
[TD]Jan [/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[TD]Total[/TD]
[TD][/TD]
[TD]Staff 1[/TD]
[TD]Staff 2[/TD]
[TD]Staff 3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Jan [/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Project 1[/TD]
[TD]£1,000[/TD]
[TD]£4,000[/TD]
[TD]£5,000[/TD]
[TD]£10,000[/TD]
[TD][/TD]
[TD]20%[/TD]
[TD]20%[/TD]
[TD]60%[/TD]
[TD]100%[/TD]
[TD][/TD]
[TD]Staff 1[/TD]
[TD]£10,100[/TD]
[TD]£7,200[/TD]
[TD]£7,300[/TD]
[TD]£24,600[/TD]
[/TR]
[TR]
[TD]Project 2[/TD]
[TD]£3,000[/TD]
[TD]£2,000[/TD]
[TD]£6,000[/TD]
[TD]£11,000[/TD]
[TD][/TD]
[TD]50%[/TD]
[TD]50%[/TD]
[TD]0%[/TD]
[TD]100%[/TD]
[TD][/TD]
[TD]Staff 2[/TD]
[TD]£1,700[/TD]
[TD]£1,800[/TD]
[TD]£4,000[/TD]
[TD]£7,500[/TD]
[/TR]
[TR]
[TD]Project 3[/TD]
[TD]£8,000[/TD]
[TD]£4,000[/TD]
[TD]£3,000[/TD]
[TD]£15,000[/TD]
[TD][/TD]
[TD]90%[/TD]
[TD]0%[/TD]
[TD]10%[/TD]
[TD]100%[/TD]
[TD][/TD]
[TD]Staff 3[/TD]
[TD]£4,200[/TD]
[TD]£7,000[/TD]
[TD]£4,700[/TD]
[TD]£15,900[/TD]
[/TR]
[TR]
[TD]Project 4[/TD]
[TD]£4,000[/TD]
[TD]£6,000[/TD]
[TD]£2,000[/TD]
[TD]£12,000[/TD]
[TD][/TD]
[TD]30%[/TD]
[TD]0%[/TD]
[TD]70%[/TD]
[TD]100%[/TD]
[TD][/TD]
[TD][/TD]
[TD]£16,000[/TD]
[TD]£16,000[/TD]
[TD]£16,000[/TD]
[TD]£48,000[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]£16,000[/TD]
[TD]£16,000[/TD]
[TD]£16,000[/TD]
[TD]£48,000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Problem is I need to do this for a number of ranges across a number of sheets, and I cannot figure it out.
I assumed I should be using a SUMIF but I cannot get it to work... feeling a bit dim
I need a formula which looks at H4:H7 and calculates the values from C4:C7 and returns the value in N4:N7
I have attached an example here, and while this works, I cannot replicate this approach on the scale I need it.
I need to calculate a value (£) based on a value in the same row (%), so if H4 = 20%, return 20% of C4, in cell N4 - simple
=SUM($H4*C4,$H5*C5,$H6*C6,$H7*C7)
[TABLE="class: grid, width: 958"]
<colgroup><col><col><col span="3"><col><col span="4"><col><col><col span="4"></colgroup><tbody>[TR]
[TD][/TD]
[TD]Jan [/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[TD]Total[/TD]
[TD][/TD]
[TD]Staff 1[/TD]
[TD]Staff 2[/TD]
[TD]Staff 3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Jan [/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Project 1[/TD]
[TD]£1,000[/TD]
[TD]£4,000[/TD]
[TD]£5,000[/TD]
[TD]£10,000[/TD]
[TD][/TD]
[TD]20%[/TD]
[TD]20%[/TD]
[TD]60%[/TD]
[TD]100%[/TD]
[TD][/TD]
[TD]Staff 1[/TD]
[TD]£10,100[/TD]
[TD]£7,200[/TD]
[TD]£7,300[/TD]
[TD]£24,600[/TD]
[/TR]
[TR]
[TD]Project 2[/TD]
[TD]£3,000[/TD]
[TD]£2,000[/TD]
[TD]£6,000[/TD]
[TD]£11,000[/TD]
[TD][/TD]
[TD]50%[/TD]
[TD]50%[/TD]
[TD]0%[/TD]
[TD]100%[/TD]
[TD][/TD]
[TD]Staff 2[/TD]
[TD]£1,700[/TD]
[TD]£1,800[/TD]
[TD]£4,000[/TD]
[TD]£7,500[/TD]
[/TR]
[TR]
[TD]Project 3[/TD]
[TD]£8,000[/TD]
[TD]£4,000[/TD]
[TD]£3,000[/TD]
[TD]£15,000[/TD]
[TD][/TD]
[TD]90%[/TD]
[TD]0%[/TD]
[TD]10%[/TD]
[TD]100%[/TD]
[TD][/TD]
[TD]Staff 3[/TD]
[TD]£4,200[/TD]
[TD]£7,000[/TD]
[TD]£4,700[/TD]
[TD]£15,900[/TD]
[/TR]
[TR]
[TD]Project 4[/TD]
[TD]£4,000[/TD]
[TD]£6,000[/TD]
[TD]£2,000[/TD]
[TD]£12,000[/TD]
[TD][/TD]
[TD]30%[/TD]
[TD]0%[/TD]
[TD]70%[/TD]
[TD]100%[/TD]
[TD][/TD]
[TD][/TD]
[TD]£16,000[/TD]
[TD]£16,000[/TD]
[TD]£16,000[/TD]
[TD]£48,000[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]£16,000[/TD]
[TD]£16,000[/TD]
[TD]£16,000[/TD]
[TD]£48,000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Problem is I need to do this for a number of ranges across a number of sheets, and I cannot figure it out.
I assumed I should be using a SUMIF but I cannot get it to work... feeling a bit dim
I need a formula which looks at H4:H7 and calculates the values from C4:C7 and returns the value in N4:N7
I have attached an example here, and while this works, I cannot replicate this approach on the scale I need it.