How to use SUMIFS() where ABS() is a condition?-CodePudding

Is there a way to include ABS() in the condition of a SUMIFS()? My table looks like this:

  A            B       C
1 H.Spread     W/L     Units
2 -0.5         W       1
3 1.5          W       1
4 0            L       -1.1
5 -0.5         L       -1.1
6 0.5          W       1

I want to do a formula that sums up the Units when the absolute value of the H.Spread is between 0 and 0.5.

This is what I tried:

=SUMIFS(C:C,ABS(A:A),">=0",ABS(A:A),"<=0.5")

This produces an error. I know there are many work arounds, but is there any way to include ABS() in my formula and have it produce what I am looking for?

Thanks in advance!

CodePudding user response：

Use SUMPRODUCT:

=SUMPRODUCT(C2:C*(ABS(A2:A)>=0)*(ABS(A2:A)<=0.5))