# Sumproduct with 2 range of variation

#### YJUNH

##### New Member
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~~

### Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Replies
3
Views
638
Replies
1
Views
493
Replies
3
Views
753
Replies
2
Views
489
Replies
3
Views
269

1,219,892
Messages
6,150,814
Members
450,985
Latest member
Andynair7

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

### Which adblocker are you using?

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

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