SUM values in different columns in one row if certain condition is met

[fxhst329]

I have portfolio of traders. Each trader makes certain amount of profit per month and has certain amount of drawdown per month. I want to be able to control which traders are part of the portfolio, so trader is either ACTIVE or DISABLED. I need to sumup monthly performance (Profit/Loss & Drawdown) for only ACTIVE traders.


So if Trader 1 and Trader 3 are ACTIVE (G1, M1), for the month of January it will sum up Profit/Loss (F3 + L3) and place result into TOTAL Profit/Loss for January (C3). Also it will sum up Drawdown (G3 + M3) and place result in TOTAL Drawdown for January (D3).


So the formulas I need would be placed into C3 & D3, from there I want to be able to select them and grab bottom right corner of D3 and drag it all the way down to row 14 so TOTAL Profit/Loss & Drawdown is automatically calculated for all months.


Original table has like 5 years of data and maybe 30 traders, so without automation it's very complicated and I just can't come up with solution to this.


Can somebody help me with this please?

[TABLE="width: 948"]
<colgroup><col span="2"><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD] [/TD]
[TD][/TD]
[TD]TOTAL[/TD]
[TD] [/TD]
[TD][/TD]
[TD]Trader 1[/TD]
[TD]ACTIVE[/TD]
[TD][/TD]
[TD]Trader 2[/TD]
[TD]DISABLED[/TD]
[TD][/TD]
[TD]Trader 3[/TD]
[TD]ACTIVE[/TD]
[/TR]
[TR]
[TD]Month[/TD]
[TD][/TD]
[TD]Profit /Loss[/TD]
[TD]Drawdown[/TD]
[TD][/TD]
[TD]Profit /Loss[/TD]
[TD]Drawdown[/TD]
[TD][/TD]
[TD]Profit /Loss[/TD]
[TD]Drawdown[/TD]
[TD][/TD]
[TD]Profit /Loss[/TD]
[TD]Drawdown[/TD]
[/TR]
[TR]
[TD]January[/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]February[/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]March[/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]April[/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]May[/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]June[/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]July[/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]August[/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]8[/TD]
[TD][/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]8[/TD]
[TD][/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]September[/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD]October[/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]November[/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]11[/TD]
[TD][/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]11[/TD]
[TD][/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]11[/TD]
[/TR]
[TR]
[TD]December[/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]12[/TD]
[TD][/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]12[/TD]
[TD][/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]12[/TD]
[/TR]
</tbody>[/TABLE]

 

[Andrew Poulsom]

Formula in C3 copied down and across:

=SUMIFS(F3:L3,$G$1:$M$1,"ACTIVE",F$2:L$2,C$2)