Excel IF/AND/OR statement

Question

appreciate the help:

I'm trying to write two IF/AND/OR statements to satisfy the following I have five columns,

1) if all of the columns are positive numbers OR some of the columns are positive numbers and some contain "NA" then I need to return "Positive".

2) if all of the columns are negative numbers OR some of the columns are negative numbers and some contain "NA" then I need to return "Negative".

My first expression to identify number 1 is working as expected:

=IF(OR(AND(B2>=0, C2>=0,D2>=0,E2>=0,F2>=0,G2>=0,H2>=0), AND(B2="NA", C2="NA",D2="NA",E2="NA",F2="NA",G2="NA",H2="NA")), "POSITIVE", "ignore")

However, the same expression just changing from >=0 to <0 is not working as I would expect and is only returning "NEGATIVE" for only the rows that contain negative numbers in all the columns (i.e. combination of NAs and negative numbers are not being picked up).

=IF(OR(AND(B2<0, C2<0,D2<0,E2<0,F2<0,G2<0,H2<0), AND(B2="NA", C2="NA",D2="NA",E2="NA",F2="NA",G2="NA",H2="NA")), "NEGATIVE", "ignore")

Answer 1

Use

=IF(AND(SUM(B2:H2) = SUMIF(B2:H2,">=0",B2:H2),COLUMNS(B2:H2) = COUNT(B2:H2) + COUNTIF(B2:H2,"NA")),"Positive",IF(AND(SUM(B2:H2) = SUMIF(B2:H2,"<0",B2:H2),COLUMNS(B2:H2) = COUNT(B2:H2) + COUNTIF(B2:H2,"NA"),COUNTIFS(B2:H2,0)=0),"Negative","Ignore"))