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

[bobselff]

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

 

[Teethless mama]

Post some samples and your formula

 

[T. Valko]

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

 

[bobselff]

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

Thanks,
Bob

 

[bobselff]

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

 

[T. Valko]

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.

 

[Teethless mama]

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

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

 

[bobselff]

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

 

[bobselff]

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]

You're welcome!

FYI: the COUNTIF equivalent would be

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