count for numbers one and < another in a row of cells
 

Good Day,
I am trying to write a formula which would look down a row of cells and
give me a count if the number is greater than x and less than Y. Every one i
try (i.e. countif, sumif etc.) either returns an error in the formula or a
wrong count.

Thank you for your time and enery,

Bob

count for numbers one and < another in a row of cells
 

Post some samples and your formula

"bobselff" wrote:

Good Day,
I am trying to write a formula which would look down a row of cells and
give me a count if the number is greater than x and less than Y. Every one i
try (i.e. countif, sumif etc.) either returns an error in the formula or a
wrong count.

Thank you for your time and enery,

Bob

count for numbers one and < another in a row of cells
 

Try something like this:

Lower boundary = 10
Upper boundary = 25

if the number is greater than x and less than Y

Are you sure that's what you want? Most of the time what people really mean
is =X and <=Y. So, here's both:

10 and <25

=COUNTIF(A1:J1,"10")-COUNTIF(A1:J1,"=25")

=10 and <=25

=COUNTIF(A1:J1,"=10")-COUNTIF(A1:J1,"25")


--
Biff
Microsoft Excel MVP


"bobselff" wrote in message
...
Good Day,
I am trying to write a formula which would look down a row of cells and
give me a count if the number is greater than x and less than Y. Every one
i
try (i.e. countif, sumif etc.) either returns an error in the formula or a
wrong count.

Thank you for your time and enery,

Bob

count for numbers one and < another in a row of cells
 

Here is an example of my data and formula:
10,15,29,45
countif(range,"=15")-countif(same range,"<=29)

Thanks,
Bob


"T. Valko" wrote:

Try something like this:

Lower boundary = 10
Upper boundary = 25

if the number is greater than x and less than Y

Are you sure that's what you want? Most of the time what people really mean
is =X and <=Y. So, here's both:

10 and <25

=COUNTIF(A1:J1,"10")-COUNTIF(A1:J1,"=25")

=10 and <=25

=COUNTIF(A1:J1,"=10")-COUNTIF(A1:J1,"25")


--
Biff
Microsoft Excel MVP


"bobselff" wrote in message
...
Good Day,
I am trying to write a formula which would look down a row of cells and
give me a count if the number is greater than x and less than Y. Every one
i
try (i.e. countif, sumif etc.) either returns an error in the formula or a
wrong count.

Thank you for your time and enery,

Bob

count for numbers one and < another in a row of cells
 

Good Evening,
My data would be say 10,12,15,35,90
my formula I tried was:
countif(range,"=12")-countif(same range,"<=35") I am looking for an answer
of 3
meaning I want to count for all the numbers between 12 and 35 inclusive.
Thanks,

Bob


"Teethless mama" wrote:

Post some samples and your formula

"bobselff" wrote:

Good Day,
I am trying to write a formula which would look down a row of cells and
give me a count if the number is greater than x and less than Y. Every one i
try (i.e. countif, sumif etc.) either returns an error in the formula or a
wrong count.

Thank you for your time and enery,

Bob

count for numbers one and < another in a row of cells
 

Post the *actual real formula* you tried and tell us what result you got and
what result you expected.

If you follow the syntax I suggested in my other reply it should work. If it
doesn't then I suspect you may have data problems. The data you have may
look like numbers but are in fact TEXT. There may be leading/trailing space
characters that you can't see that will cause problems. This is a common
problem.

--
Biff
Microsoft Excel MVP


"bobselff" wrote in message
...
Here is an example of my data and formula:
10,15,29,45
countif(range,"=15")-countif(same range,"<=29)

Thanks,
Bob


"T. Valko" wrote:

Try something like this:

Lower boundary = 10
Upper boundary = 25

if the number is greater than x and less than Y

Are you sure that's what you want? Most of the time what people really
mean
is =X and <=Y. So, here's both:

10 and <25

=COUNTIF(A1:J1,"10")-COUNTIF(A1:J1,"=25")

=10 and <=25

=COUNTIF(A1:J1,"=10")-COUNTIF(A1:J1,"25")


--
Biff
Microsoft Excel MVP


"bobselff" wrote in message
...
Good Day,
I am trying to write a formula which would look down a row of cells
and
give me a count if the number is greater than x and less than Y. Every
one
i
try (i.e. countif, sumif etc.) either returns an error in the formula
or a
wrong count.

Thank you for your time and enery,

Bob

count for numbers one and < another in a row of cells
 

Try like this:
=COUNTIF(range,"=12")-COUNTIF(range,"35")

or
=SUM(COUNTIF(range,{"=12","35"})*{1,-1})


"bobselff" wrote:

Good Evening,
My data would be say 10,12,15,35,90
my formula I tried was:
countif(range,"=12")-countif(same range,"<=35") I am looking for an answer
of 3
meaning I want to count for all the numbers between 12 and 35 inclusive.
Thanks,

Bob


"Teethless mama" wrote:

Post some samples and your formula

"bobselff" wrote:

Good Day,
I am trying to write a formula which would look down a row of cells and
give me a count if the number is greater than x and less than Y. Every one i
try (i.e. countif, sumif etc.) either returns an error in the formula or a
wrong count.

Thank you for your time and enery,

Bob

count for numbers one and < another in a row of cells
 

I want to thank all of you for your help, it appears the formula which gave
me the correct answer is:
=sumproduct (($a$2:$a$22=0)*($a$2:$a$22<=30))

Both of you were a great help,

Thank you for your time and effort.

Bob

"T. Valko" wrote:

Post the *actual real formula* you tried and tell us what result you got and
what result you expected.

If you follow the syntax I suggested in my other reply it should work. If it
doesn't then I suspect you may have data problems. The data you have may
look like numbers but are in fact TEXT. There may be leading/trailing space
characters that you can't see that will cause problems. This is a common
problem.

--
Biff
Microsoft Excel MVP


"bobselff" wrote in message
...
Here is an example of my data and formula:
10,15,29,45
countif(range,"=15")-countif(same range,"<=29)

Thanks,
Bob


"T. Valko" wrote:

Try something like this:

Lower boundary = 10
Upper boundary = 25

if the number is greater than x and less than Y

Are you sure that's what you want? Most of the time what people really
mean
is =X and <=Y. So, here's both:

10 and <25

=COUNTIF(A1:J1,"10")-COUNTIF(A1:J1,"=25")

=10 and <=25

=COUNTIF(A1:J1,"=10")-COUNTIF(A1:J1,"25")


--
Biff
Microsoft Excel MVP


"bobselff" wrote in message
...
Good Day,
I am trying to write a formula which would look down a row of cells
and
give me a count if the number is greater than x and less than Y. Every
one
i
try (i.e. countif, sumif etc.) either returns an error in the formula
or a
wrong count.

Thank you for your time and enery,

Bob

count for numbers one and < another in a row of cells
 

I want to thank all of you for your help, it appears the formula which gave
me the correct answer is:
=sumproduct (($a$2:$a$22=0)*($a$2:$a$22<=30))

Both of you were a great help,

Thank you for your time and effort.

Bob


"Teethless mama" wrote:

Try like this:
=COUNTIF(range,"=12")-COUNTIF(range,"35")

or
=SUM(COUNTIF(range,{"=12","35"})*{1,-1})


"bobselff" wrote:

Good Evening,
My data would be say 10,12,15,35,90
my formula I tried was:
countif(range,"=12")-countif(same range,"<=35") I am looking for an answer
of 3
meaning I want to count for all the numbers between 12 and 35 inclusive.
Thanks,

Bob


"Teethless mama" wrote:

Post some samples and your formula

"bobselff" wrote:

Good Day,
I am trying to write a formula which would look down a row of cells and
give me a count if the number is greater than x and less than Y. Every one i
try (i.e. countif, sumif etc.) either returns an error in the formula or a
wrong count.

Thank you for your time and enery,

Bob

count for numbers one and < another in a row of cells
 

You're welcome!

FYI: the COUNTIF equivalent would be

=COUNTIF($A$2:$A$22,"=0")-COUNTIF($A$2:$A$22,"30")

--
Biff
Microsoft Excel MVP


"bobselff" wrote in message
...
I want to thank all of you for your help, it appears the formula which gave
me the correct answer is:
=sumproduct (($a$2:$a$22=0)*($a$2:$a$22<=30))

Both of you were a great help,

Thank you for your time and effort.

Bob

"T. Valko" wrote:

Post the *actual real formula* you tried and tell us what result you got
and
what result you expected.

If you follow the syntax I suggested in my other reply it should work. If
it
doesn't then I suspect you may have data problems. The data you have may
look like numbers but are in fact TEXT. There may be leading/trailing
space
characters that you can't see that will cause problems. This is a common
problem.

--
Biff
Microsoft Excel MVP


"bobselff" wrote in message
...
Here is an example of my data and formula:
10,15,29,45
countif(range,"=15")-countif(same range,"<=29)

Thanks,
Bob


"T. Valko" wrote:

Try something like this:

Lower boundary = 10
Upper boundary = 25

if the number is greater than x and less than Y

Are you sure that's what you want? Most of the time what people really
mean
is =X and <=Y. So, here's both:

10 and <25

=COUNTIF(A1:J1,"10")-COUNTIF(A1:J1,"=25")

=10 and <=25

=COUNTIF(A1:J1,"=10")-COUNTIF(A1:J1,"25")


--
Biff
Microsoft Excel MVP


"bobselff" wrote in message
...
Good Day,
I am trying to write a formula which would look down a row of cells
and
give me a count if the number is greater than x and less than Y.
Every
one
i
try (i.e. countif, sumif etc.) either returns an error in the
formula
or a
wrong count.

Thank you for your time and enery,

Bob