Allocation Formula

[ExcelAl1]

Hi, I was wondering if there was a formula to automatically allocate cost. I have included a picture of what I am trying to do. The columns in "BLUE" have the department cost for bonuses being alloted for employees. Each department has a department code. Each department has to pay a portion of the bonuses so what I want to do is to see if there is a formula that would automatically evaluate all the employee bonus payouts in "GREEN" and assign the department code in the first column of the green columns so they would add up to the amount allocated to each department in the blue columns. Thank You

 

[Bosquedeguate]

test20230822.xlsm
	A	B	C	D
1	dept		allocated
2	148	10.71	10.00	0.71
3	167	37.30	37.50	-0.20
4	113	44.05	44.00	0.05
5	141	62.31	62.50	-0.19
6	569	173.52	173.50	0.02
7	156	244.86	245.00	-0.14
8	287	281.76	281.50	0.26
csvtest

Cell Formulas
Range	Formula
D2:D8	D2	=B2-C2

try this (see formula in column C

test20230822.xlsm
	G	H	I
1	Dept	Emp	Bonus
2	569	Employee A	86.75
3	287	Employee B	70.38
4	569	Employee C	86.75
5	113	Employee D	44.00
6	141	Employee E	62.50
7	156	Employee F	245.00
8	287	Employee G	70.38
9	287	Employee H	70.38
10	287	Employee H	70.38
csvtest

Cell Formulas
Range	Formula
I2:I10	I2	=VLOOKUP(G2,$A$2:$C$8,3,FALSE)/COUNTIF($G$2:$G$99,G2)