Comparing 2 ranges of values to a single cell

0
votes

I want to compare 2 ranges of values to a single cell. I want to compare each row of both columns combined. Not just any value in column A and any in column B. It must be A1 & B1, A2 & B2, etc.

Example:

A1 : 1
A2 : 5
A3 : 7

B1 : 2
B2 : 3
B3 : 6

C1 : 11

I could use this formula:

=IF(OR(AND(A1<C1,B1>C1),AND(A2<C1,B2>C1),AND(A3<C1,B3>C1)),0,C1)

But I need to check a range of more than 100 cells so using AND would bloat the formula.

Which formula do I use?

Thanks in advance.

1
excelexcel-formula

1 Answers

0
votes

use COUNTIF():

=IF(COUNTIFS(A:A,"<"& C1,B:B,">" & C1),0,C1)

If any fit then it returns 0 other wise C1

enter image description here