Can't figure out why combo of IF,AND,OR,COUNTIF statements in formula isn't working

I wrote a simple formula in column H that checks if the Item# in col. A is on a sheet named SharePoint. If it's not, the cell will say 'Add to SP.'

Please Login or Register  to view this content.

But what I really want is for the cell to say this only if sheet SharePoint doesn't haven the Item# AND columns D or F say one of the phrases in the OR statement below. I added a combination of AND and OR statements to the above formula to accomplish this. This formula is in column I.

Please Login or Register  to view this content.

8ktlx.png
These are a couple of test cases. Neither item# is in sheet SharePoint (which the simple formula in col. H correctly diagnoses), AND they have one of the phrases in the OR statement. So column I should say Add to SP, but the formula just returns a #N/A error.

I had found a syntax error that I thought was causing the problem and fixed it, but I still get an error. I have also messed around with the order/placement of the AND and OR statements, but no success - and, I feel like the current order I have my formula in makes logical sense. What might the problem be? There must be something wrong with the logic of the formula...

I would also like to introduce myself. This is my first time posting on this site. I currently work in supply chain and work with large excel spreadsheets all day, every day. Up until now I've been using StackExchange since that's what a colleague of mine recommended, but I came across excelforum today and I am excited (I feel like the Excel part of StackExchange gets neglected).

F2 is a #N/A error so as soon as your formula tries to compare it to "Supplier late" it causes an error and that's what the formula returns. You'd need something like:

=IF(AND(COUNTIF(SharePoint!N:N,A2)=0,OR(D2={"Transferred","Needs Validation","No PPAP Required","Closed W/O Approval"},IFERROR(F2,"")="Supplier Late")),"Add to SP","")