Automatically split totals across multiple lines

MartinW22

New Member
Hi,

Please see pictures below to highlight the issue, as I cannot use the plugin on a work computer to copy the formulas.

Basically I am to automate column F. The current approach would be to set it equal to column D which is a VLOOKUP then I would search for multiple site operatives and apply the split formula. Looking for a way to automate this. Operatives can work any number of sites (1-3 usually) and therefore the total pay would need to be split based on the hours across the different sites. I was previously doing this manually but would like a way to automate it. There aren't specific operatives that work multiple sites each week, nor are they always in the same rows. However, if an operative works multiple sites they are in subsequent lines from each other (i.e. row 2 and 3 for operative 1). Columns A-C are in the form of a dataset which I copy. Column D is a VLOOKUP from a different dataset. The common variable is the operative reference (column A).

If you have any suggestions they would be greatly appreciated. If you need any further information do not hesitate to ask.

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Alex Blakenburg

Well-known Member
Do you just mean something like this ?

20210906 SUMIFS Apportionment.xlsx
ABCDEFG
1OperativeSiteHoursTotal PayAdjust for split sites
21A12500352.94
31C5500147.06
42B10659659.00
53C15541541.00
64A9695297.86
74B8695264.76
84C4695132.38
9
Sheet1
Cell Formulas
RangeFormula
F2:F8F2=ROUND(\$D2*(\$C2/SUMIFS(\$C\$2:\$C\$8,\$A\$2:\$A\$8,A2)),2)

MartinW22

New Member
Do you just mean something like this ?

20210906 SUMIFS Apportionment.xlsx
ABCDEFG
1OperativeSiteHoursTotal PayAdjust for split sites
21A12500352.94
31C5500147.06
42B10659659.00
53C15541541.00
64A9695297.86
74B8695264.76
84C4695132.38
9
Sheet1
Cell Formulas
RangeFormula
F2:F8F2=ROUND(\$D2*(\$C2/SUMIFS(\$C\$2:\$C\$8,\$A\$2:\$A\$8,A2)),2)
Thanks this works perfectly, not sure why I didn't realise it was this simple. Case of me trying to overengineer something using VLOOKUP for the first row and then offsetting by the count of the employee reference is what made me end up asking here. Don't know why SUMIF didn't come to mind. Thanks!

Alex Blakenburg

Well-known Member
No problem Thanks for letting me know. Glad I could help.

Replies
3
Views
169
Replies
1
Views
166
Replies
1
Views
210
Replies
2
Views
296
Replies
0
Views
85

Threads
1,147,482
Messages
5,741,409
Members
423,658
Latest member
Kumaradas

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

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