Using ABS in Countifs

[Eyale]

Am I able to use the ABS function on this formula somehow?


=SUM(COUNTIFS('Sheet1'!A$2:A$191,{"0*","1*","2*","3*","4*","5*","6*","7*","8*","9*"},'Sheet1'!E$2:E$191,">"&F8))


I tried the following but it is not working:


=SUM(COUNTIFS('Sheet1'!A$2:A$191,{"0*","1*","2*","3*","4*","5*","6*","7*","8*","9*"},ABS('Sheet1'!E$2:E$191),">"&F8))


Is there something I am missing here? Tried all sorts of strategies around it and I am not able to do it. Please help.

 

[steve the fish]

I think this will work:

=SUMPRODUCT(--(ISNUMBER(0+LEFT('Sheet1'!A2:A191,1))),--(ABS('Sheet1'!E2:E191)>F8))

 

[RoryA]

Please take a minute to read the forum rules on cross-posting, and follow them in future. Thanks.

You could use:

=SUM(COUNTIFS(Sheet1!A$2:A$191,{"0*","1*","2*","3*","4*","5*","6*","7*","8*","9*"},Sheet1!E$2:E$191,{">";"<-"}&F8))

 

[Eyale]

@stevetheFish thank you but that did not work.
@RoryA that is magic! Thank you very much! If you do not mind, may you please explain this bit so that I am not making wrong assumptions and not missing finer details: {">";"<-"}&F8

 

[RoryA]

You have two arrays in that formula. The first one (the {"0*", "1*" etc}) is a horizontal array due to the commas. The second one is the {">";"<-"}&F8 part which creates a vertical array due to the semicolons which is formed as:
">"&F8
"<-"&F8
so it covers the situations of greater than the value in F8 and less than the negated value of F8, which is the same effect as using the absolute value. By having one vertical array and one horizontal one, you get every combination of the two arrays.

 

[steve the fish]

Im surprised that sumproduct doesnt work. I cant think of a scenario where it produces a different result to the countifs unless you dont have numbers in column E.