I need a formula to find numbers in a column that are exactly within a
range, like a page range.

Example desired result:
Column A
7
10
100

Chapter 1 pp. 7-8: YES
Chapter 2 pp. 9-12: YES
Chapter 3 pp. 13-25: NO
Chapter 4 pp. 26-100: YES

As it is, I keep getting a YES for Chapter 3, with this formula:

=IF(AND(IF(COUNTIF(A1:A3,
"=13")0,TRUE,FALSE),IF(COUNTIF(A1:A3,"<=25")0,T RUE,FALSE))=TRUE,C1&",
","")

That's because the formula is finding that there is a number =13
(100) and a number that is <=25 (10), yet none of these numbers is in
the page range of 13-25.

What function do I use to do this correctly?

On Feb 3, 8:53 am, wrote:
I need a formula to find numbers in a column that are exactly within a
range, like a page range.

Example desired result:
Column A
7
10
100

Chapter 1 pp. 7-8: YES
Chapter 2 pp. 9-12: YES
Chapter 3 pp. 13-25: NO
Chapter 4 pp. 26-100: YES

As it is, I keep getting a YES for Chapter 3, with this formula:

=IF(AND(IF(COUNTIF(A1:A3,
"=13")0,TRUE,FALSE),IF(COUNTIF(A1:A3,"<=25")0,T RUE,FALSE))=TRUE,C1&",
","")

That's because the formula is finding that there is a number =13
(100) and a number that is <=25 (10), yet none of these numbers is in
the page range of 13-25.

What function do I use to do this correctly?

Hi Alfred,

I'm not sure about the latter part of your formula, however if you are
after a NO when none of the page numbers in column A are included in
pp 13-25 and a YES when one or more of the column A numbers are
included are included in pp 13-25 then use...

=IF(COUNTIF(A:A,"=13")-COUNTIF(A:A,"25")0,"YES","NO")

Ken Johnson

As far as I can tell, this is what I needed! Thanks!

On Feb 2, 5:31 pm, "Ken Johnson" wrote:
On Feb 3, 8:53 am, wrote:



I need a formula to find numbers in a column that are exactly within a
range, like a page range.

Example desired result:
Column A
7
10
100

Chapter 1 pp. 7-8: YES
Chapter 2 pp. 9-12: YES
Chapter 3 pp. 13-25: NO
Chapter 4 pp. 26-100: YES

As it is, I keep getting a YES for Chapter 3, with this formula:

=IF(AND(IF(COUNTIF(A1:A3,
"=13")0,TRUE,FALSE),IF(COUNTIF(A1:A3,"<=25")0,T RUE,FALSE))=TRUE,C1&",
","")

That's because the formula is finding that there is a number =13
(100) and a number that is <=25 (10), yet none of these numbers is in
the page range of 13-25.

What function do I use to do this correctly?

Hi Alfred,

I'm not sure about the latter part of your formula, however if you are
after a NO when none of the page numbers in column A are included in
pp 13-25 and a YES when one or more of the column A numbers are
included are included in pp 13-25 then use...

=IF(COUNTIF(A:A,"=13")-COUNTIF(A:A,"25")0,"YES","NO")

Ken Johnson

Hi Alfred,

You're welcome.
Thanks for the feedback and the great rating.
JMB's solution is a better one since it only uses one function call.
Thanks to JMB.

Ken Johnson

where B1 = 13 and C1 = 25

=IF(SUMPRODUCT(--($A$1:$A$3=B1),--($A$1:$A$3<=C1)), "YES", "NO")

modify ranges as needed.


" wrote:

I need a formula to find numbers in a column that are exactly within a
range, like a page range.

Example desired result:
Column A
7
10
100

Chapter 1 pp. 7-8: YES
Chapter 2 pp. 9-12: YES
Chapter 3 pp. 13-25: NO
Chapter 4 pp. 26-100: YES

As it is, I keep getting a YES for Chapter 3, with this formula:

=IF(AND(IF(COUNTIF(A1:A3,
"=13")0,TRUE,FALSE),IF(COUNTIF(A1:A3,"<=25")0,T RUE,FALSE))=TRUE,C1&",
","")

That's because the formula is finding that there is a number =13
(100) and a number that is <=25 (10), yet none of these numbers is in
the page range of 13-25.

What function do I use to do this correctly?