Formula to test non-contiguous blanks/non-blanks by row

Question

I want to create a formula that will only give a true response if specific columns are blank and the other specific columns have a value in them. For example I only want a statement to be true if columns A, C, D, and F are blank, and columns B, E, G, and H have values in them.

Edit not by OP, from comments

"other specific columns have a 1 in them"
"separate result for each row"

however also

"single true false"

Answer 1

This should work.

=IF(AND(A1="",B1<>"",C1="",D1="",E1<>"",F1="",G1<>"",H1<>""),"True","False")

Answer 2

The functions you require in Excel are the logical AND, OR and NOT functions - plus the ISBLANK function. The syntax is a bit crazy as with all things excel, but you get there in the end. For your example, the following formula works out okay and is reasonably readable!:

=AND(ISBLANK($A3),ISBLANK($C3),ISBLANK($D3),ISBLANK($F3),NOT(ISBLANK($B3)),NOT(ISBLANK($E3)),NOT(ISBLANK($G3)))

You should edit the row value according to the row you are interested in. I've locked the columns with the "$". As you copy and and paste the forumla between rows the paste will modify the row number, but keep the column numbers the same, even if you paste it into a different column.

Answer 3

Perhaps:

=IF(AND(COUNTA(A:A)+COUNTA(C:C)+COUNTA(D:D)+COUNTA(F:F)=0,COUNTA(B:B)<>0,COUNTA(E:E)<>0,COUNTA(G:G)<>0,COUNTA(H:H)<>0),TRUE,FALSE)