Help needed for conditional sum formula

[tarun_1]

Hi,

We can use Sumproduct or Sumifs formula to get conditional sum from a row.

But what if formula has to choose which column to take for making conditional sum?

Attached is excel file as an example. Our source data is spread across 3000 rows and 200 columns & output data template is spread across 30000 rows

-regards,
tarun_1

 

[Hui]

Tarun

In J18: Have a look at:
=SUMPRODUCT(($A$3:$A$9=$F18)*($B$3:$B$9=$G18)*($C$3:$C$9=$E18)*($D$3:$D$9=$H18)*($E$3:$E$9=$I18)*OFFSET($E$3:$E$9,,MATCH($C18&$D18,$F$1:$M$1&$F$2:$M$2,0)))
Copy down as required

 

[sudipto3003]

hi tarun,
=SUMPRODUCT(DATA,(HEAD1=$F18)*(HEAD2=$G18)*(HEAD3=$E18)*(HEAD4=$H18)*(HEAD5=$I18)*(COLUMN1=$C18)*(COLUMN2=$D18))
go through the attach file may help you.

 

[tarun_1]

super
-thanks