Range Formula

[Jay]

I'm trying to count the number of cells which contain values within a given
range. For example, I want to count all the cell in a column which contain a
value between 50,000 and 100,000. I've tried using the Countif formula given
as an example by microsoft but the results are wrong. The formula looks like
this:

countif(y2:y268, ">=50000")-countif(y2:y268, "<=100000")

Does anyone know why I'm getting the wrong results and is there another way
to do this? Thanks

 

[T. Valko]

Try it like this:

=COUNTIF(Y2:Y268, ">=50000")-COUNTIF(Y2:Y268, ">100000")

 

[David Biddulph]

I suggest you look again at the example. I think it's more likely that the
suggestion is something like
=COUNTIF(Y2:Y268, ">=50000")-COUNTIF(Y2:Y268, ">100000") if you want to
include values of 50000 and 100000, or
=COUNTIF(Y2:Y268, ">50000")-COUNTIF(Y2:Y268, ">=100000") if you want to
excluide those limit values.

 

[Jay]

Biff,

That worked, but can you explain why. I'm having problems wrapping my head
around it. To me that formula is say count cells greater than or equal to
50000 and cells greater that or equal to 100000. Any insight you could
provide is greatly appreciated.

 

[David Biddulph]

I assume that what you want is those cells which are greater than or equal
to 50000 AND less than or equal to 100000.
Hence you want to count the cells greater than or equal to 50000, but from
those you do NOT want to include those greater than 100000, hence the
subtraction.

 

[iliace]

He's taking the count of values greater or equal to 50,000 and
subtracting the count of values greater than 100,000. This results in
the count of values >=50,000 and <=100,000.

 

[Jay]

Gotcha. That makes perfect sense now. Thanks for the explanation on that, I
was staring to pull my hair out....

 

[T. Valko]

This may not seem intuitive but the logic works.

The first COUNTIF is counting all values >=50,000

The second COUNTIF is counting all values >100,000

The result of the second COUNTIF is subtracted from the first COUNTIF so in
essence you get:

COUNT IF range is >=50,000 and <=100,000

44250
50000
51269
100000
102500

=COUNTIF(rng,">=50000) = 4
=COUNTIF(rng">100000) = 1

So:

=COUNTIF(rng, ">=50000")-COUNTIF(rng, ">100000") = 3

50000, 51269, 100000

Another way that may seem more intuitive:

=SUMPRODUCT(--(Y2:Y268>=50000),--(Y2:Y268<=100000))

However, the COUNTIF method is more efficient.