# 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

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

#### 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

1,147,482
Messages
5,741,407
Members
423,657
Latest member
Medrok2021

### 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?

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