I have the following numbers 12,34,-15,-23,-5,45,-50 and would like to find the count of numbers where the abs(x)>=15, which in this case is 5 Is this possible with the use of COUNTIFS ?
Asked
Active
Viewed 3,228 times
0
-
1Are these in a single cell or separate cells? – shrivallabha.redij May 29 '20 at 05:51
-
2COUNTIFS can take multiple conditions, so have one for >= 15 and another for <= -15 – chris neilsen May 29 '20 at 06:05
-
@shrivallabha.redij separate cells. Would like to see a solution with COUNTIFS.. – yathrakaaran May 29 '20 at 06:20
-
@chrisneilsen trying to find the absolute value, not the range. – yathrakaaran May 29 '20 at 06:23
-
1It would be `=COUNTIFS(A2:A8,">=15")+COUNTIFS(A2:A8,"<=-15")` or `=SUM(COUNTIFS(A2:A8,{">=15","<=-15"}))`. – shrivallabha.redij May 29 '20 at 06:25
1 Answers
4
Based on the inputs, following formulas can be used
=SUMPRODUCT((ABS(A2:A8)>=15)+0)
and COUNTIFS implementation would be as below
=COUNTIFS(A2:A8,">=15")+COUNTIFS(A2:A8,"<=-15")
=SUM(COUNTIFS(A2:A8,{">=15","<=-15"}))
As COUNTIFS requires range as input and therefore any math operation is not possible!
shrivallabha.redij
- 5,832
- 1
- 12
- 27