(Msg. 1) Posted: Mon Jul 28, 2008 7:04 pm
Post subject: countif question using two negative number criterias Archived from groups: microsoft>public>excel>misc (more info?)

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.

(Msg. 2) Posted: Mon Jul 28, 2008 7:14 pm
Post subject: RE: countif question using two negative number criterias [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)

--
Wigi
http://www.wimgielis.be = Excel/VBA, soccer and music

"JD Long" wrote:

> 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")

(Msg. 3) Posted: Mon Jul 28, 2008 7:19 pm
Post subject: RE: countif question using two negative number criterias [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)

You can also set up a helper row to class this according to aging and then
countif by the aging class
--
Tips for Excel, Word, PowerPoint and Other Applications
http://www.kan.org/tips

"Wigi" wrote:

> You can always use cell references:
>
> =COUNTIF('Janet-CURRENT MTH'!$M$6:$M$250,"=<" & A1)
>
> with -91 in cell A1.
>
> --
> Wigi
> http://www.wimgielis.be = Excel/VBA, soccer and music
>
>
> "JD Long" wrote:
>
> > 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")

(Msg. 4) Posted: Tue Jul 29, 2008 2:35 pm
Post subject: Re: countif question using two negative number criterias [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)

"JD Long" wrote in message

>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 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

You can post new topics in this forum You can reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum