Feeling dim: How to simplify this formula? SUMIF

S

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.
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Sort by date Sort by votes
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
MNOP

<tbody>
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Jan[/TD]
[TD="align: center"]Feb[/TD]
[TD="align: center"]Mar[/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]Staff 1[/TD]
[TD="align: center"]£10,100[/TD]
[TD="align: center"]£7,200[/TD]
[TD="align: center"]£7,300[/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]Staff 2[/TD]
[TD="align: center"]£1,700[/TD]
[TD="align: center"]£1,800[/TD]
[TD="align: center"]£4,000[/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]Staff 3[/TD]
[TD="align: center"]£4,200[/TD]
[TD="align: center"]£7,000[/TD]
[TD="align: center"]£4,700[/TD]

</tbody>
Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]N4[/TH]
[TD="align: left"]=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)))[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"][/TH]
[TD="align: left"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


Does that work?
 
Last edited:
Upvote 0
Actually, the second index-match is overkill:


Excel 2010
BCDEFGHIJKLMNOPQ
Project 1
Project 2
Project 3
Project 4

<tbody>
[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: center"]Jan[/TD]
[TD="align: center"]Feb[/TD]
[TD="align: center"]Mar[/TD]
[TD="align: center"]Total[/TD]
[TD="align: right"][/TD]
[TD="align: center"]Staff 1[/TD]
[TD="align: center"]Staff 2[/TD]
[TD="align: center"]Staff 3[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Jan[/TD]
[TD="align: center"]Feb[/TD]
[TD="align: center"]Mar[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]£1,000[/TD]
[TD="align: center"]£4,000[/TD]
[TD="align: center"]£5,000[/TD]
[TD="align: center"]£10,000[/TD]
[TD="align: right"][/TD]
[TD="align: center"]20%[/TD]
[TD="align: center"]20%[/TD]
[TD="align: center"]60%[/TD]
[TD="align: center"]100%[/TD]
[TD="align: right"][/TD]
[TD="align: center"]Staff 1[/TD]
[TD="align: center"]£10,100[/TD]
[TD="align: center"]£7,200[/TD]
[TD="align: center"]£7,300[/TD]
[TD="align: center"]£24,600[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]£3,000[/TD]
[TD="align: center"]£2,000[/TD]
[TD="align: center"]£6,000[/TD]
[TD="align: center"]£11,000[/TD]
[TD="align: right"][/TD]
[TD="align: center"]50%[/TD]
[TD="align: center"]50%[/TD]
[TD="align: center"]0%[/TD]
[TD="align: center"]100%[/TD]
[TD="align: right"][/TD]
[TD="align: center"]Staff 2[/TD]
[TD="align: center"]£1,700[/TD]
[TD="align: center"]£1,800[/TD]
[TD="align: center"]£4,000[/TD]
[TD="align: center"]£7,500[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]£8,000[/TD]
[TD="align: center"]£4,000[/TD]
[TD="align: center"]£3,000[/TD]
[TD="align: center"]£15,000[/TD]
[TD="align: right"][/TD]
[TD="align: center"]90%[/TD]
[TD="align: center"]0%[/TD]
[TD="align: center"]10%[/TD]
[TD="align: center"]100%[/TD]
[TD="align: right"][/TD]
[TD="align: center"]Staff 3[/TD]
[TD="align: center"]£4,200[/TD]
[TD="align: center"]£7,000[/TD]
[TD="align: center"]£4,700[/TD]
[TD="align: center"]£15,900[/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]£4,000[/TD]
[TD="align: center"]£6,000[/TD]
[TD="align: center"]£2,000[/TD]
[TD="align: center"]£12,000[/TD]
[TD="align: right"][/TD]
[TD="align: center"]30%[/TD]
[TD="align: center"]0%[/TD]
[TD="align: center"]70%[/TD]
[TD="align: center"]100%[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]£16,000[/TD]
[TD="align: center"]£16,000[/TD]
[TD="align: center"]£16,000[/TD]
[TD="align: center"]£48,000[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: center"]£16,000[/TD]
[TD="align: center"]£16,000[/TD]
[TD="align: center"]£16,000[/TD]
[TD="align: center"]£48,000[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]N4[/TH]
[TD="align: left"]=SUMPRODUCT(INDEX($H$4:$J$7,,MATCH($M4,$H$3:$J$3,0)),C$4:C$7)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"][/TH]
[TD="align: left"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

This will work as well
 
Upvote 0
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?
 
Upvote 0
stuartmacdonald said:
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?
Click to expand...

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
Click to expand...

So [SUMPRODUCT of sheet1] + [SUMPRODUCT of sheet2] + [SUMPRODUCT of sheet3] and so on
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Solved
Help with Random Formulas
2
Replies
10
Views
2K
SanjayGMusafir
SanjayGMusafir
Ramballah
  • Question Question
Small formula for calculation
2
Replies
10
Views
1K
Ramballah
Ramballah
MrDB4Excel
  • Solved
If Exist in a Column Range
Replies
8
Views
2K
DanteAmor
DanteAmor
K
  • Solved
SUMIFS or SUMPRODUCT or something else :(
2
Replies
11
Views
1K
Peter_SSs
Peter_SSs
S
  • Question Question
Streamlining a long, manual formula
Replies
1
Views
887
SewStage
S

Forum statistics

Threads
1,223,911
Messages
6,175,325
Members
452,635
Latest member
laura12345

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top