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

Try it like this:

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


--
Biff
Microsoft Excel MVP


"Jay" wrote in message
...
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

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.
--
David Biddulph

"Jay" wrote in message
...
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

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.

"T. Valko" wrote:

Try it like this:

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


--
Biff
Microsoft Excel MVP


"Jay" wrote in message
...
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

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.
--
David Biddulph

"Jay" wrote in message
...
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.

"T. Valko" wrote:

Try it like this:

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


--
Biff
Microsoft Excel MVP


"Jay" wrote in message
...
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

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.

On Dec 12, 4:44 pm, Jay wrote:
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.

"T. Valko" wrote:
Try it like this:

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

--
Biff
Microsoft Excel MVP

"Jay" wrote in message
...
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

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

"David Biddulph" wrote:

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.
--
David Biddulph

"Jay" wrote in message
...
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.

"T. Valko" wrote:

Try it like this:

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


--
Biff
Microsoft Excel MVP


"Jay" wrote in message
...
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

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.

--
Biff
Microsoft Excel MVP


"Jay" wrote in message
...
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.

"T. Valko" wrote:

Try it like this:

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


--
Biff
Microsoft Excel MVP


"Jay" wrote in message
...
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