# counting ranges of negative time values

#### nn992

##### New Member
Hello everyone,

I have giant file containing various time values. I have to sort those times according to frequency. What I managed to do is to sort them like:

00:00:00 123 occurences

between 0 and 1 456 occurences

between 1 and 2 789 occurences

etc...

What I cant do is same thing, but for NEGATIVE numbers. I was using countifs function for positive values, but for negatives it always responds with 0 as a result of occurences.

Thanks

### Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

#### steve the fish

##### Well-known Member
The COUNTIFS is as simple as:

=COUNTIFS(A:A,"<0")

If it produces 0 as the answer then your times are not times, they will be textual.

#### nn992

##### New Member
when I enter countifs(P:P;"<00:00:00";P:P;">-00:02:00")

he should respond with frequency numbers between 0 and -2 minutes, right? I get response 0, even though there is bunch on numbers which fulfill this criterion...

#### steve the fish

##### Well-known Member
What does this do? Excel doesnt really like negative time.

=COUNTIFS(P:P,"<0",P:P,">"&-TIME(0,2,0))

#### nn992

##### New Member
still returns zero as ansver in all cases...

#### NARAYANK991

##### Board Regular
Hi ,

As already mentioned , Excel displays negative time as a series of # symbols.

However , if you change your column format to General , you can easily see all the negative numbers.

A time value such as 00:02:00 is numerically 0.00139 ; thus when you have a negative number which is the equivalent of 00:02:00 , it should be -0.00139 ; when you change your column format to General , are there values between -0.00139 and 0 ?

If so , Steve's formula :

=COUNTIFS(P:P,"<0",P:P,">"&-TIME(0,2,0))

should work.