Count Negative in Range

Question

I am attempting to count the number of occurrences that contain a * in a specific numeric range using the COUNTIFS function in Google Sheets. The function works for a positive range, but fails when I add a negative. I have tried escaping the negative and adding parens but nothing seems to work.

Works - correctly returns a value of 1, because 0.01* is between 0 and .1 and contains an *

0.01*
.8*
*
-0.08
-0.08*
=COUNTIFS(J62:J66, ">0.000\*",J62:J66, "<0.1\*")

Does Not Work - expected return value of 1, because -.08* is between 0 and -.1 and contains an *.

-.3*
-.8*
-*
-0.08
-0.08*
=COUNTIFS(H62:H66, ">-0.1\*",H62:H66, "<0.000\*")

edit to address @pnuts comment

If I change -.3* to -0.3* and -.8* to -0.8* the function returns a value of 2, which is not what I expect.

Thanks for catching that. Should make more sense now that I fixed the explanation for why the first one work. — Dementedpenguin, Aug 17 '17 at 01:33

score 0 · Answer 1 · answered Aug 19 '17 at 16:14

The problem is NOT the negative ranges. The problem is simply the * part.

Works - correctly returns a value of 1, because 0.01* is between 0 and .1 and contains an *

   0.01*
   .8*
    *
    -0.08
    -0.08*
   =COUNTIFS(J62:J66, ">0.000\*",J62:J66, "<0.1\*")

This doesn't work. It gives you 1 ONLY because of the 4th entry -0.08 without the *. This satisfies J62:J66, "<0.1\*", but not J62:J66, ">0.000\*". Hence a Total of 1 is arrived. To test, Simply change only this fourth number to -0.08*and you won't get 1. This is NOT because 0.01* is between 0 and .1 and contains an *

You cannot use * in a countif. Simply replace the * and COUNTIF will work splendidly.

       B1 =ARRAYFORMULA(--REGEXREPLACE(T(A1:A5),"\*",""))

This will remove the * from all your text and put it in helper column B. There will be errors for those without the * . But that's fine, since you only want to count those numbers with * in it. Now simply use COUNTIF on the B column.

TheMaster · Answer 2 · 2017-08-17T06:08:35.763

-1

        = ARRAYFORMULA(COUNTIFS(ROUND(--REGEXEXTRACT(T(A1:A5),"((?:\+|\-){0,1}(?:\d+){0,5}(?:\.\d+))(?:\*)"),2),">0.0",ROUND(--REGEXEXTRACT(T(A1:A5),"((?:\+|\-){0,1}(?:\d+){0,5}(?:\.\d+))(?:\*)"),2),"<=0.8"))

This should works. Problems with your original formula is: You can't use text as numbers. Numbers should be extracted from text and counted.

edited Aug 17 '17 at 06:08

answered Aug 17 '17 at 06:01

TheMaster

45,448
6
62
85

@dementedpenguin Check this formula. This should work for negative ranges – TheMaster Aug 17 '17 at 20:01
It works for positive numbers, but not negative which is what I'm stuck on. Thanks for the suggestion @anonymous! – Dementedpenguin Aug 19 '17 at 15:38
This works for negative ranges too...... `= ARRAYFORMULA(COUNTIFS(ROUND(--REGEXEXTRACT(T(A1:A5),"((?:\+|\-){0,1}(?:\d+){0,5}(?:\.\d+))(?:\*)"),2),">-0.1",ROUND(--REGEXEXTRACT(T(A1:A5),"((?:\+|\-){0,1}(?:\d+){0,5}(?:\.\d+))(?:\*)"),2),"<0"))` `-.3* -.8* -* -0.08 -0.08*` Returns 1 as expected by you. – TheMaster Aug 19 '17 at 15:49

Count Negative in Range

2 Answers2