I need to find the number of cells in a column that are greater than value x
and less than value y.

I ahve tried vairations of punctuation spacing etc. with no luck. I am
beginning that it is not possible.

Anyone have any ideas?

One way using SUMPRODUCT ..

Try something like this in say, B1:
=SUMPRODUCT((A1:A1005)*(A1:A100<25))

B1 returns the count of values within A1:A100
which are greater than 5 but less than 25

Note that entire col references cannot be used
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"compliance data" wrote:
I need to find the number of cells in a column that are greater than value x
and less than value y.

I ahve tried vairations of punctuation spacing etc. with no luck. I am
beginning that it is not possible.

Anyone have any ideas?

Use two COUNTIFs.

=COUNTIF(A1:A10,"<=9")-COUNTIF(A1:A10,"<5")

This will return the count of numbers in A1:A10 that are between 5 and 9.
Change the "<" and "<=" comparison operators to fit your needs.



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

"compliance data" <compliance wrote in
message ...
I need to find the number of cells in a column that are greater than value
x
and less than value y.

I ahve tried vairations of punctuation spacing etc. with no luck. I am
beginning that it is not possible.

Anyone have any ideas?

let me try it and get back to you. Thanks

"Max" wrote:

One way using SUMPRODUCT ..

Try something like this in say, B1:
=SUMPRODUCT((A1:A1005)*(A1:A100<25))

B1 returns the count of values within A1:A100
which are greater than 5 but less than 25

Note that entire col references cannot be used
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"compliance data" wrote:
I need to find the number of cells in a column that are greater than value x
and less than value y.

I ahve tried vairations of punctuation spacing etc. with no luck. I am
beginning that it is not possible.

Anyone have any ideas?

Elegent in its simplicity ... thanks I beleive that this appraoch will meet
my needs.

"Chip Pearson" wrote:

Use two COUNTIFs.

=COUNTIF(A1:A10,"<=9")-COUNTIF(A1:A10,"<5")

This will return the count of numbers in A1:A10 that are between 5 and 9.
Change the "<" and "<=" comparison operators to fit your needs.



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

"compliance data" <compliance wrote in
message ...
I need to find the number of cells in a column that are greater than value
x
and less than value y.

I ahve tried vairations of punctuation spacing etc. with no luck. I am
beginning that it is not possible.

Anyone have any ideas?

tried it this am worked very well, thanks

"Max" wrote:

One way using SUMPRODUCT ..

Try something like this in say, B1:
=SUMPRODUCT((A1:A1005)*(A1:A100<25))

B1 returns the count of values within A1:A100
which are greater than 5 but less than 25

Note that entire col references cannot be used
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"compliance data" wrote:
I need to find the number of cells in a column that are greater than value x
and less than value y.

I ahve tried vairations of punctuation spacing etc. with no luck. I am
beginning that it is not possible.

Anyone have any ideas?

Tried it this morning and it did not work. I was looking for values .5 and
<2.0. for this paericular data set there are 76 cells in the column. 3 cells
were =2.0 and 72 were <=.5. The answer should have been 1 got 69??
"Chip Pearson" wrote:

Use two COUNTIFs.

=COUNTIF(A1:A10,"<=9")-COUNTIF(A1:A10,"<5")

This will return the count of numbers in A1:A10 that are between 5 and 9.
Change the "<" and "<=" comparison operators to fit your needs.



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

"compliance data" <compliance wrote in
message ...
I need to find the number of cells in a column that are greater than value
x
and less than value y.

I ahve tried vairations of punctuation spacing etc. with no luck. I am
beginning that it is not possible.

Anyone have any ideas?

Glad to hear that !
Thanks for feeding back
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"compliance data" wrote in
message ...
tried it this am worked very well, thanks

In the interim while awaiting Chip's response, some thoughts ..

Did you use something like:
=COUNTIF(A:A,"<2")-COUNTIF(A:A,"<=0.5")

If so, and you're not getting the correct results, then possibly
there's some source numbers which are text numbers, not real numbers.
In which case, you could do this to convert the entire col to real
numbers. Select an empty cell, copy it, then select col A, right-click
paste special Check "Add" OK.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
compliance data wrote:
Tried it this morning and it did not work. I was looking for values .5 and
<2.0. for this particular data set there are 76 cells in the column. 3 cells
were =2.0 and 72 were <=.5. The answer should have been 1 got 69??

I checked they are all real numbers

"Max" wrote:

In the interim while awaiting Chip's response, some thoughts ..

Did you use something like:
=COUNTIF(A:A,"<2")-COUNTIF(A:A,"<=0.5")

If so, and you're not getting the correct results, then possibly
there's some source numbers which are text numbers, not real numbers.
In which case, you could do this to convert the entire col to real
numbers. Select an empty cell, copy it, then select col A, right-click
paste special Check "Add" OK.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
compliance data wrote:
Tried it this morning and it did not work. I was looking for values .5 and
<2.0. for this particular data set there are 76 cells in the column. 3 cells
were =2.0 and 72 were <=.5. The answer should have been 1 got 69??

compliance data wrote...
Tried it this morning and it did not work. I was looking for values .5 and
<2.0. for this paericular data set there are 76 cells in the column. 3 cells
were =2.0 and 72 were <=.5. The answer should have been 1 got 69??

Show the exact formula you used. Was it

=COUNTIF(YourRangeHere,"<2")-COUNTIF(YourRangeHere,"<=.5")

?

Your result, 69, suspiciously equals 72 - 3, so it seems you may have
used

=COUNTIF(YourRangeHere,"<=.5")-COUNTIF(YourRangeHere,"=2")

Reread Chip's response.

"Chip Pearson" wrote:
Use two COUNTIFs.

=COUNTIF(A1:A10,"<=9")-COUNTIF(A1:A10,"<5")

This will return the count of numbers in A1:A10 that are between 5 and 9.
Change the "<" and "<=" comparison operators to fit your needs.
....

The order of the comparisons is essential. The first COUNTIF call needs
to count all items up to the TOP end of your range, and the second
COUNTIF call needs to count all items below the BOTTOM end of your
range. Since your range is 0.5 to 2.0 exclusive, the first COUNTIF
call's criterion should be <2, and the second one's criterion <=.5.

Another alternative might make it easier.

=COUNTIF(YourRangeHere,"<2")+COUNTIF(YourRangeHere ,".5")
-COUNT(YourRangeHere)

compliance data wrote:
I checked they are all real numbers

I'm out of further guesses here
as to why you're still getting incorrect results ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Thank you Harlan I changed the direction of < and got the correct response.
Thanks to all of you who have contributed towards this solution!!


"Harlan Grove" wrote:

compliance data wrote...
Tried it this morning and it did not work. I was looking for values .5 and
<2.0. for this paericular data set there are 76 cells in the column. 3 cells
were =2.0 and 72 were <=.5. The answer should have been 1 got 69??

Show the exact formula you used. Was it

=COUNTIF(YourRangeHere,"<2")-COUNTIF(YourRangeHere,"<=.5")

?

Your result, 69, suspiciously equals 72 - 3, so it seems you may have
used

=COUNTIF(YourRangeHere,"<=.5")-COUNTIF(YourRangeHere,"=2")

Reread Chip's response.

"Chip Pearson" wrote:
Use two COUNTIFs.

=COUNTIF(A1:A10,"<=9")-COUNTIF(A1:A10,"<5")

This will return the count of numbers in A1:A10 that are between 5 and 9.
Change the "<" and "<=" comparison operators to fit your needs.
....

The order of the comparisons is essential. The first COUNTIF call needs
to count all items up to the TOP end of your range, and the second
COUNTIF call needs to count all items below the BOTTOM end of your
range. Since your range is 0.5 to 2.0 exclusive, the first COUNTIF
call's criterion should be <2, and the second one's criterion <=.5.

Another alternative might make it easier.

=COUNTIF(YourRangeHere,"<2")+COUNTIF(YourRangeHere ,".5")
-COUNT(YourRangeHere)