Sumproduct with 2 range of variation

[YJUNH]

Dear All,

Im working on a raw data as below and i need to know how does sumproduct can help me to generate a formula with a range of variation for both the column and the row in red and green respectively.

Trial Balance

GL account	Description Function->	32000	32100	32120	32200	33000	33120	33140	33220	33240	33300	34000
101200			Accum.depr. for IT Software	0.00	0.00	0.00	100.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
141000			IT-Hardware	200	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
141100			Accum.depr. for IT-Hardware	0.00	0.00	1000	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
142000			Office Furniture/F & F	0.00	0.00	0.00	0.00	0.00	999	0.00	0.00	0.00	0.00	0.00
142100			Accum.d.Office Furniture/F & F	0.00	0.00	0.00	0.00	0.00	0.00	0.00	555	0.00	0.00	0.00
​

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

i want to do it in a way as below but tried multiple times and searches on the internet to no avail. =(

i need a formula if function (4 Variation) = 32200 or 32210 or 32220 or 32222 with a combination GL code of 5 variation and will generate the result for me based on above table.

5- DIRECT: BLUE COLLARS	Amount	Function 1	Function 2	Function 3	Function 4	GL Code	GL Code	GL Code	GL Code	GL Code
Salaries - Full Time	(Result)	32200	32210	32220	32222	400100	400200	400300	25252	55000
Salaries - Part Time/Temp	-	32200	32210	12345	54321
Salaries - incentives	-	32200	32210
Overtime	-	32200	32210
Bonus	-	32200	32210			400600

<colgroup><col><col><col><col span="2"><col><col><col span="4"></colgroup><tbody>
</tbody>

My predecessor has used the below formula on her own table but its too profound for me to understand.

SUMPRODUCT((COUNTIF($D4:$L4,TrialBalanceDXY!$B$29:$B$278)>0)*(COUNTIF($C4,"*"&LOOKUP(COLUMN(TrialBalanceDXY!$H$28:$BF$28)+0.5,COLUMN(TrialBalanceDXY!$H$28:$BF$28)/(LEN(TrialBalanceDXY!$H$28:$BF$28)>0),TrialBalanceDXY!$H$28:$BF$28)&"*")>0)*TrialBalanceDXY!$H$29:$BF$278)/1000

Any help will be greatly appreciated~~