ABS

[Zubair]

Hi Team,

Can you please teach me how to add ABS in my given formula, I want ABS to convert negative to positive.

=IF(MATCH($D$201,$FK$1:$FV$1,0)<=MATCH($B$2,$FK$1:$FV$1,0),SUMIFS(TB!$J:$J,TB!$G:$G,$A214,TB!$F:$F,F$2)+SUMIFS(F$3:F$199,$B$3:$B$199,$A214),0)

 

[Fluff]

Which part of that formula need to be absolute?

 

[Zubair]

2 figures

SUMIFS(TB!$J:$J

SUMIFS(F$3:F$199

 

[Radoslaw Poprawski]

if you want to have the result in ABsolutes:

ABS(SUMIFS(TB!$J:$J))
ABS(SUMIFS(F$3:F$199))

if you want to sum absolutes:

SUMIFS(ABS(TB!$J:$J))
SUMIFS(ABS(F$3:F$199))

 

[Fluff]

Ok, how about

=IF(MATCH($D$201,$FK$1:$FV$1,0)<=MATCH($B$2,$FK$1:$FV$1,0),SUMPRODUCT((TB!$G:$G=$A214)*(TB!$F:$F,F$2)*(ABS(TB!$J:$J)))+SUMPRODUCT(($B$3:$B$199=$A214)*(ABS(F$3:F$199))),0)

although it's best not to use whole column references in this type of formula.

 

[Fluff]

@Radoslaw Poprawski
Your 2nd option cannot work.

 

[Radoslaw Poprawski]

@Fluff
Then why is it working in this example:

Book1
	F	G	H	I	J	K
1
2				=SUM(ABS(I5:I14))	=SUM(J5#)
3		Result:	37	95	95
4
5		Data:	15	15	15
6			4	4	4
7			4	4	4
8			8	8	8
9			18	18	18
10			-6	-6	6
11			17	17	17
12			-4	-4	4
13			-9	-9	9
14			-10	-10	10
15
Sheet1

Cell Formulas
Range	Formula
I2:J2	I2	=FORMULATEXT(I3)
H3	H3	=SUM(H5:H14)
I3	I3	=SUM(ABS(I5:I14))
J3	J3	=SUM(J5#)
J5:J14	J5	=ABS(I5:I14)
Dynamic array formulas.

 

[Fluff]

Because it's not what you originally suggested & also not what the OP is after.

 

[Joe4]

@Fluff
Then why is it working in this example:

Those aren't the same formulas. You posted:

SUMIFS(ABS(TB!$J:$J))
SUMIFS(ABS(F$3:F$199))

If you try either of those, you will get errors, saying you do not have enough arguments.

 

[Radoslaw Poprawski]

hmmm
I tested it myself - you were right

Book1
	F	G	H	I	J	K	L	M	N
1
2				=SUM(ABS(I6:I15))	=SUM(J6#)	Criteria1	Criteria2	Criteria3
3		Result:	37	95	95	23	49	23
4						23	49	23
5
6		Criteria1	15	15	15	15
7		Criteria1	4	4	4	4
8		Criteria1	4	4	4	4
9		Criteria2	8	8	8	8
10		Criteria2	18	18	18	18
11		Criteria2	-6	-6	6	6
12		Criteria2	17	17	17	17
13		Criteria3	-4	-4	4	4
14		Criteria3	-9	-9	9	9
15		Criteria3	-10	-10	10	10
16
Sheet1

Cell Formulas
Range	Formula
I2:J2	I2	=FORMULATEXT(I3)
K2:M2	K2	=TRANSPOSE(UNIQUE(G6:G15))
H3	H3	=SUM(H6:H15)
I3	I3	=SUM(ABS(I6:I15))
J3	J3	=SUM(J6#)
K3:M3	K3	=SUMIFS(K6#,G6:G15,K2#)
K4:M4	K4	=SUM(ABS($I$6:$I$15)*($G$6:$G$15=K2))
J6:J15	J6	=ABS(I6:I15)
K6:K15	K6	=ABS(I6:I15)
Dynamic array formulas.

Altho I have no idea sumifs works on absolute range but when I put abs onto my range it stops working.

neverthe less. standard sum function works properly