Feeling dim: How to simplify this formula? SUMIF

[stuartmacdonald]

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)

	Jan	Feb	Mar	Total		Staff 1	Staff 2	Staff 3				Jan	Feb	Mar
Project 1	£1,000	£4,000	£5,000	£10,000		20%	20%	60%	100%		Staff 1	£10,100	£7,200	£7,300	£24,600
Project 2	£3,000	£2,000	£6,000	£11,000		50%	50%	0%	100%		Staff 2	£1,700	£1,800	£4,000	£7,500
Project 3	£8,000	£4,000	£3,000	£15,000		90%	0%	10%	100%		Staff 3	£4,200	£7,000	£4,700	£15,900
Project 4	£4,000	£6,000	£2,000	£12,000		30%	0%	70%	100%			£16,000	£16,000	£16,000	£48,000
	£16,000	£16,000	£16,000	£48,000

<colgroup><col><col><col span="3"><col><col span="4"><col><col><col span="4"></colgroup><tbody>
</tbody>

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.

 

[MartyS]

Try this: https://www.dropbox.com/s/h0bvn2in1he74zu/stuartmacdonald_Example.xlsx?dl=0

(If clicking on the link doesn't work.. copy and paste it into the browser addressbar..)

Excel 2010

	M	N	O	P
3		Jan	Feb	Mar
4	Staff 1	£10,100	£7,200	£7,300
5	Staff 2	£1,700	£1,800	£4,000
6	Staff 3	£4,200	£7,000	£4,700

<tbody>
</tbody>

Sheet2​

Worksheet Formulas Cell Formula N4 =SUMPRODUCT(INDEX($C$4:$E$7,,MATCH(N$3,$C$3:$E$3,0)),INDEX($H$4:$J$7,,MATCH($M4,$H$3:$J$3,0))) <tbody> </tbody>

<tbody>
</tbody>

Does that work?

 

[MartyS]

Actually, the second index-match is overkill:


Excel 2010

	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
3		Jan	Feb	Mar	Total		Staff 1	Staff 2	Staff 3				Jan	Feb	Mar
4	Project 1	£1,000	£4,000	£5,000	£10,000		20%	20%	60%	100%		Staff 1	£10,100	£7,200	£7,300	£24,600
5	Project 2	£3,000	£2,000	£6,000	£11,000		50%	50%	0%	100%		Staff 2	£1,700	£1,800	£4,000	£7,500
6	Project 3	£8,000	£4,000	£3,000	£15,000		90%	0%	10%	100%		Staff 3	£4,200	£7,000	£4,700	£15,900
7	Project 4	£4,000	£6,000	£2,000	£12,000		30%	0%	70%	100%			£16,000	£16,000	£16,000	£48,000
8		£16,000	£16,000	£16,000	£48,000

<tbody>
</tbody>

Sheet2​

Worksheet Formulas Cell Formula N4 =SUMPRODUCT(INDEX($H$4:$J$7,,MATCH($M4,$H$3:$J$3,0)),C$4:C$7) <tbody> </tbody>

<tbody>
</tbody>

This will work as well

 

[stuartmacdonald]

Thank you very much, SUMPRODUCT works....
Now I need to add together the SUMPRODUCT from 5 sheets. I have tried a number things, but this is what makes most sense to me, but it's not working

=SUMPRODUCT(Sheet1!AK4:AK40,Sheet1!Q4:Q40)+(Sheet2!AK4:AK7,Sheet2!Q4:Q7)+(Sheet3!AJ4:AJ25,Sheet3!Q4:Q25)+('Sheet4'!AM4:AM15,'Sheet4'!Q4:Q15)+(Sheet5!AJ4:AJ12,Sheet5!Q4:Q12)

Any suggestions?

 

[MartyS]

Thank you very much, SUMPRODUCT works....
Now I need to add together the SUMPRODUCT from 5 sheets. I have tried a number things, but this is what makes most sense to me, but it's not working

=SUMPRODUCT(Sheet1!AK4:AK40,Sheet1!Q4:Q40)+(Sheet2!AK4:AK7,Sheet2!Q4:Q7)+(Sheet3!AJ4:AJ25,Sheet3!Q4:Q25)+('Sheet4'!AM4:AM15,'Sheet4'!Q4:Q15)+(Sheet5!AJ4:AJ12,Sheet5!Q4:Q12)

Any suggestions?

I'm not sure (wrt Forum Rules) whether this needs to be a new post or not.. (as it's a different topic).


You need to REPEAT the SUMPRODUCT for each sheet..

e.g. =SUMPRODUCT(Sheet1!AK4:AK40,Sheet1!Q4:Q40) + SUMPRODUCT(Sheet2!AK4:AK40,Sheet2!Q4:Q40) + SUMPRODUCT(Sheet3!AK4:AK40,Sheet3!Q4:Q40)



as in

add together the SUMPRODUCT

So [SUMPRODUCT of sheet1] + [SUMPRODUCT of sheet2] + [SUMPRODUCT of sheet3] and so on