Applying additional logic (sign flip) within SUMIFS statement

[karlosDavid]

Can anyone advise on how to streamline the aggregation logic below - I'd like to remove the column: "normalized_x" entirely and move the logic to within the aggregation statement.

Thanks for any help

Book1
	B	C	D	E	F	G	H	I	J	K	L	M
2
3		commodity	type	x	normalized_x
4		GAS	BUY	-15	15
5		GAS	SELL	1	1
6		GAS	BUY	7	-7
7		POWER	BUY	14	-14
8		POWER	SELL	5	5
9		POWER	SELL	-6	-6
10
11			Aggregation
12			GAS	gains	16		(sum of positive values in normalized_x for GAS)
13				losses	-7		(sum of negative values in normalized_x for GAS)
14			POWER	gains	5
15				losses	-20
16
Sheet1

Cell Formulas
Range	Formula
F4:F9	F4	=IF(D4="BUY",E4*-1,E4)
F12	F12	=SUMIFS(F4:F9,F4:F9,">0",C4:C9,D12)
F13	F13	=SUMIFS(F4:F9,F4:F9,"<0",C4:C9,D12)
F14	F14	=SUMIFS(F4:F9,F4:F9,">0",C4:C9,D14)
F15	F15	=SUMIFS(F4:F9,F4:F9,"<0",C4:C9,D14)

 

[Fluff]

Hi & welcome to MrExcel.
For F12 how about

=LET(a,E4:E9*IF(D4:D9="buy",-1,1),SUM(IF((a>0)*(C4:C9=D12),a)))

 

[RoryA]

Or:
Gains: =SUM(SUMIFS($E$4:$E$9,$D$4:$D$9,{"BUY","SELL"},$E$4:$E$9,{"<",">"}&0,$C$4:$C$9,D12)*{-1,1})
Losses: =SUM(SUMIFS($E$4:$E$9,$D$4:$D$9,{"BUY","SELL"},$E$4:$E$9,{">","<"}&0,$C$4:$C$9,D12)*{-1,1})

 

[karlosDavid]

My hero, thanks!

 

[karlosDavid]

Or:
Gains: =SUM(SUMIFS($E$4:$E$9,$D$4:$D$9,{"BUY","SELL"},$E$4:$E$9,{"<",">"}&0,$C$4:$C$9,D12)*{-1,1})
Losses: =SUM(SUMIFS($E$4:$E$9,$D$4:$D$9,{"BUY","SELL"},$E$4:$E$9,{">","<"}&0,$C$4:$C$9,D12)*{-1,1})

Thanks also

 

[Fluff]

Glad we could help & thanks for the feedback.