Using "IF" Function & "OR" Function for a range of cells

Question

Hi,

I understand the IF and OR functions, but this is the first time that I'm using these functions in a single cell to look at a range of other cells and get an answer.

I'll try to explain as simply as I can what I would like the formula to do:

Formula is in cell A1....

IF any cell or cells in range: B3 thru B31 says:

"Waitng On Invoice"   OR   "Waiting On Statement"

Then say if True in A1: "Do Not Pay".

If False and none of the cells in B3 thru B31 say the above.    Then say: "Line Item can be Paid"

I've tried to write this formula a number of different ways, but I'm just not getting the syntax right. Excel wont' let me keep the formula as I write it.

I think it may have something do to with dealing with the range B3:B31.  Is this where I need to use an array formula?

Thank-you for your time in advance,

Amy

Answer 1

Hi,

If you only have just two conditions, you can use,

=IF(COUNT(MATCH({"Waiting On Invoice","Waiting On Statement"},B3:B31,0)),"Do Not Pay","Line item can be Paid")

Or

=IF(SUM(COUNTIF(B3:B31,{"Waiting On Invoice","Waiting On Statement"})),"Do Not Pay","Line item can be Paid")

Answer 2

Hi,

Try this

=IF(ISNUMBER(LOOKUP(2,1/MATCH({"Waiting on invoice";"Waiting on statement"},$B$3:$B$31,0))),"Do not Pay","Line item can be paid")

Hope this helps.

Answer 3

Very nice way to implement the "OR"

Answer 4

Hi,

Try this

=IF(SUMPRODUCT(--(B3:B31={"Waiting on Invoice","Waiting on Statement"}))>0,"Do not pay","Line item can be paid")

Answer 5

=IF(COUNTIF(B3:B31,"Waiting On Invoice")+COUNTIF(B3:B31,"Waiting On Statement")>0,"Do Not Pay","Line item can be Paid")