How to SUM Absolute Values in SUMIFS function?

[ibmy]

Hi expert Excellers,

Currently I use these 2 formula to calculate sum of set data at certain range of times. Both produce same result.
Now, I interested in sum of absolute value. The data consist of postive and negative values.

1)

=SUMIFS($G$2:$G$200000,$D$2:$D$200000,">="&K5,$D$2:$D$200000,"<"&TIME(HOUR(K5)+4,MINUTE(K5),0))

2)

=SUMIFS($G$2:$F$200000,$D$2:$D$200000,”>=”&K11,$D$2:$D$200000,”< “&K12)

G column : Set of data
D column : 24 hour time
K cell : Time Range from D column

I searched on web, and found out that I need to use ABS function, but do not know how to put the function in current formula as criteria2?/criteria 3?

 

[RoryA]

You can use:

=SUM(SUMIFS($G$2:$G$200000,$G$2:$G$200000,{"<0",">0"},$D$2:$D$200000,">="&K5,$D$2:$D$200000,"<"&TIME(HOUR(K5)+4,MINUTE(K5),0))*{-1,1})

and

=SUM(SUMIFS($G$2:$G$200000,$G$2:$G$200000,{"<0",">0"},$D$2:$D$200000,">="&K11,$D$2:$D$200000,"<"&K12)*{-1,1})

 

[kvsrinivasamurthy]

For 1st formula Try this array formula

=SUM(IF(($D$2:$D$200000>=K5)*($D$2:$D$200000<TIME(HOUR(K5)+4,MINUTE(K5),0)),IF($G$2:$G$10>0,$G$2:$G$10,-1*($G$2:$G$10)),""))

Similarly for 2nd formula.

To enter ARRAY formula
Copy and paste the formula in cell
Press F2
Press Ctrl+Shift+Enter together
Excel covers the formula with {}.