Automatically split totals across multiple lines

[MartinW22]

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.

 

[Alex Blakenburg]

Do you just mean something like this ?

20210906 SUMIFS Apportionment.xlsx
	A	B	C	D	E	F	G
1	Operative	Site	Hours	Total Pay		Adjust for split sites
2	1	A	12	500		352.94
3	1	C	5	500		147.06
4	2	B	10	659		659.00
5	3	C	15	541		541.00
6	4	A	9	695		297.86
7	4	B	8	695		264.76
8	4	C	4	695		132.38
9
Sheet1

Cell Formulas
Range	Formula
F2:F8	F2	=ROUND($D2*($C2/SUMIFS($C$2:$C$8,$A$2:$A$8,A2)),2)

 

[MartinW22]

Do you just mean something like this ?

20210906 SUMIFS Apportionment.xlsx
	A	B	C	D	E	F	G
1	Operative	Site	Hours	Total Pay		Adjust for split sites
2	1	A	12	500		352.94
3	1	C	5	500		147.06
4	2	B	10	659		659.00
5	3	C	15	541		541.00
6	4	A	9	695		297.86
7	4	B	8	695		264.76
8	4	C	4	695		132.38
9
Sheet1

Cell Formulas
Range	Formula
F2:F8	F2	=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]

No problem Thanks for letting me know. Glad I could help.