countif question using two negative number criterias

  • Thread starter Thread starter JD Long
  • Start date Start date
J

JD Long

I am trying to use the countif function to identify account aging. For
example how many fall within a range of numbers: over -90; -90 to -61; -60
to -31; -30 to -1 range. The greater than symbol and less than symbol are
throwing it off. I'm not sure how to correct it. Any help would be greatly
appreciated.

This is how I have it set up:

=COUNTIF('Janet-CURRENT MTH'!$M$6:$M$250,"=<-91")

=COUNTIF('Janet-CURRENT MTH'!$M$6:$M$250,"<=-90")+COUNTIF('Janet-CURRENT
MTH'!$M$6:$M$250,"<=-61")

=COUNTIF('Janet-CURRENT MTH'!$M$6:$M$250,"<=-60")+COUNTIF('Janet-CURRENT
MTH'!$M$6:$M$250,"<=-31")

=COUNTIF('Janet-CURRENT MTH'!$M$6:$M$250,"<=-31")+COUNTIF('Janet-CURRENT
MTH'!$M$6:$M$250,"<=-1")
 
You can always use cell references:

=COUNTIF('Janet-CURRENT MTH'!$M$6:$M$250,"=<" & A1)

with -91 in cell A1.
 
You can also set up a helper row to class this according to aging and then
countif by the aging class
 
JD Long said:
I am trying to use the countif function to identify account aging. For
example how many fall within a range of numbers: over -90; -90
to -61; -60
to -31; -30 to -1 range. The greater than symbol and less than symbol are
throwing it off. I'm not sure how to correct it. Any help would be
greatly
appreciated.

This is how I have it set up:

=COUNTIF('Janet-CURRENT MTH'!$M$6:$M$250,"=<-91")

=COUNTIF('Janet-CURRENT MTH'!$M$6:$M$250,"<=-90")+COUNTIF('Janet-CURRENT
MTH'!$M$6:$M$250,"<=-61")

=COUNTIF('Janet-CURRENT MTH'!$M$6:$M$250,"<=-60")+COUNTIF('Janet-CURRENT
MTH'!$M$6:$M$250,"<=-31")

=COUNTIF('Janet-CURRENT MTH'!$M$6:$M$250,"<=-31")+COUNTIF('Janet-CURRENT
MTH'!$M$6:$M$250,"<=-1")
Click to expand...

You need a minus sign not a plus:

In cell A1 put
=COUNTIF('Janet-CURRENT MTH'!$M$6:$M$250,"<=-91")

then in A2 put
=COUNTIF('Janet-CURRENT > MTH'!$M$6:$M$250,"<=-61") - A1

then in A3 put
=COUNTIF('Janet-CURRENT > MTH'!$M$6:$M$250,"<=-31") - A1 - A2

etc
 
Back
Top