IF, AND and OR

[jamescooper]

Trying to get this to work, but I can't quite get it

IF(AND(OR(MATCH(D17,K:K,0)>0,MATCH(E17,L:L,0)>0),SUMPRODUCT(COUNTIF(M:M,TEXT(LEFT(F17,4)-10+ROW(M$1:M$21)-1,"0000")&" "&TRIM(MID(F17,6,100))))>0),"",

The OR statement in bold, the first match is greater than 0 and the second is not, therefore this should be true. Then the SUMPRODUCT part of the formula is also TRUE, so it should return blank, i.e. ""

I am not sure if I have this part correct...OR(MATCH(D17,K:K,0)>0,MATCH(E17,L:L,0)>0)

Any ideas please?

 

[KRice]

I'm assuming you left off the closing right parenthesis. Just to confirm, are you using the MATCH>0 construction only to confirm that a match is found for either one of the two OR arguments, and if so, you want TRUE? If a match is not found for either one, an #N/A error will occur, and by using two matches in an OR, it is conceivable you'll have OR(TRUE,#N/A), which is not TRUE, but instead will yield a result of #N/A.

 

[jamescooper]

Just to confirm, are you using the MATCH>0 construction only to confirm that a match is found for either one of the two OR arguments, and if so, you want TRUE?

That is correct.

I do receive a TRUE and an #N/A and yields an #N/A

How would I modify it do you think?

Thanks.

 

[KRice]

You might try something like this for the OR(MATCH...replace OR(MATCH(D17,K:K,0)>0,MATCH(E17,L:L,0)>0) with COUNTIF(K:K,D17)+COUNTIF(L:L,E17)

 

[KRice]

If either finds a match, the number of matches (>0) will be found, otherwise if no match is found, a 0 will be returned for that part of the equation.

 

[jamescooper]

So I modify to that:

IF(COUNTIF(K:K,D17)+COUNTIF(L:L,E17))>0,"","No match"

 

[KRice]

Sorry, you probably get my point, but my suggest wasn't quite correct...replace OR(MATCH(D17,K:K,0)>0,MATCH(E17,L:L,0)>0) with (COUNTIF(K:K,D17)+COUNTIF(L:L,E17))>0

 

[KRice]

IF(AND( (COUNTIF(K:K,D17)+COUNTIF(L:L,E17))>0 ,SUMPRODUCT...
leave the AND and the rest of your formula from SUMPRODUCT onward

 

[jamescooper]

Thank you very much; that was helpful. I cannot believe I couldn't solve that - I am better than this.

Must be the time.

 

[KRice]

There might be better ways to do this, but the suggestion simply checks to see if the number of matches (D17 in the K column or E17 in the L column) are greater than 0, and if so, you'll get the TRUE that you want, otherwise you'll get a FALSE. I think that's what the MATCHes were attempting to do, but this avoids the #N/A error. Otherwise, the rest of your formula stays intact. Glad to help!