Automatically split totals across multiple lines

MartinW22

New Member
Joined
Sep 6, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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).
split1.PNG

split2.PNG


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

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
2,243
Office Version
  1. 365
Platform
  1. Windows
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)
 
Solution

MartinW22

New Member
Joined
Sep 6, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Feb 23, 2021
Messages
2,243
Office Version
  1. 365
Platform
  1. Windows
No problem Thanks for letting me know. Glad I could help.
 

Forum statistics

Threads
1,147,482
Messages
5,741,402
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?

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
Top