ABS of a column

[Leticia]

Hi! I have a database and I want to obtain the absolute values based on some conditions.

TYPE (A)	CODE (B)	BOOK (C)	VALUE (D)	ABS VALUE (E)
A-XXX	1998	YYY	5	5
P-XXX	1998	YYY	-3	3
O-XXX	3003	ZZZ	6	6
A-XXX	1998	ZZZ	-9	9
P-XXX	4605	YYY	4	4
O-XXX	2008	YYY	-13	13

The original contains the the first 3, so given the massive amount of data of the real database, for my calculus I would rather include the operation of the absolute value in the formula instead of adding a new column. (Is there any possible way of doing it? Maybe with dynamic arrays?)

The formula in E5 =SUM(SUMIFS($E:$E;$A:$A;L$4&"*";$C:$C;"<>ZZZ";$B:$B;$H$5:$I$5))

Thank you!

 

[Fluff]

I would use the helper column as it's almost certainly more efficient, but you can try

=SUM(FILTER(ABS($D$2:$D$100),(LEFT($A$2:$A$100,1)=L4)*($C$2:$C$100<>"ZZZ")*(($B$2:$B$100=$H$5)+($B$2:$B$100=$I$5))))

 

[Leticia]

I would use the helper column as it's almost certainly more efficient, but you can try

=SUM(FILTER(ABS($D$2:$D$100),(LEFT($A$2:$A$100,1)=L4)*($C$2:$C$100<>"ZZZ")*(($B$2:$B$100=$H$5)+($B$2:$B$100=$I$5))))

Great! It worked thank you!

 

[Fluff]

You're welcome & thanks for the feedback.