2

What I am trying to achieve is a formula that checks the top row for "all are blank" or "all are not blank" in specified ranges. Depending on the conditions the cell with the formula gives back 1 of 3 words or leaves it blank.

I further illustrate this with the colours in the following images

transfer

send

receive

The formula I have so far:

=ArrayFormula(ifs((not(isblank(A1:B1*C1:E1*G1:I1))*(isblank(J1:L1*N1:P1))),"SEND", not(isblank(A1:B1*J1:L1*N1:P1))*isblank(C1:E1*G1:I1),"RECEIVE", not(isblank(A1:B1*C1:E1*G1:I1*J1:L1*N1:P1)),"TRANSFER", ISBLANK(A1:B1+C1:E1+G1:I1+J1:L1+N1:P1),""))

I don't understand what is wrong with the formula. As for example in the case when J1:L1*N1:P1 are filled, isblank(J1:L1*N1:P1) returns false when i want that to be true.

sallux
  • 21
  • 5
  • Well, what's the problem with what you have so far? – ttarchala Feb 12 '18 at 20:58
  • Right now it gives back "TRANSFER" no matter what's in the row. so how i want it is as follows; ranges are filled="TRANSFER", any of the range J:P is not filled="SEND", any of the range C:I is not filled="RECEIVE", any of the above is FALSE=empty cell – sallux Feb 12 '18 at 21:19

2 Answers2

1

Here are the individual IF statements:

=IF((COUNTIF(A1:E1,"?*")=5)*(COUNTIF(G1:L1,"?*")=6)*(COUNTIF(N1:P1,"?*")=3)=TRUE,"TRANSFER","")

=IF((COUNTIF(A1:E1,"<>")=5)*(COUNTIF(G1:I1,"<>")=3)*(COUNTIF(J1:L1,"")=3)*COUNTIF(N1:P1,"")=3,"SEND","")

=IF((COUNTIF(C1:E1,"")=3)*(COUNTIF(G1:I1,"")=3)*(COUNTIF(J1:L1,"<>")=3)*COUNTIF(N1:P1,"<>")=3,"RECEIVE","")

Here are the nested IF statements. 

=IF((COUNTIF(A1:E1,"?*")=5)*(COUNTIF(G1:L1,"?*")=6)*(COUNTIF(N1:P1,"?*")=3)=TRUE,"TRANSFER",IF((COUNTIF(A1:E1,"<>")=5)*(COUNTIF(G1:I1,"<>")=3)*(COUNTIF(J1:L1,"")=3)*COUNTIF(N1:P1,"")=3,"SEND",IF((COUNTIF(C1:E1,"")=3)*(COUNTIF(G1:I1,"")=3)*(COUNTIF(J1:L1,"<>")=3)*COUNTIF(N1:P1,"<>")=3,"RECEIVE","")))
N.O.Davis
  • 501
  • 2
  • 10
  • 22
  • Thank you! that definitely helped me steer into the right direction. There were a couple of little mistakes relating to my specific requirements, but your statements are sound. So I adjusted your statements to my liking – sallux Feb 13 '18 at 21:58
  • Glad you were able to modify it to suit your needs. Just two notes. ISODD may not be necessary. Also, if you ever have functions in a cell which show the values, "<>" won't catch them. That's where "?*" will help. – N.O.Davis Feb 15 '18 at 19:19
0

So with the help of Nate I came up with the following formula:

=IFS(ISODD((COUNTBLANK(A1:E1)=0)*(COUNTBLANK(G1:L1)=0)*(COUNTBLANK(N1:P1)=0)),"TRANSFER", (ISODD((COUNTIF(A1:E1,"<>")=5)*(COUNTIF(G1:I1,"<>")=3)*(COUNTIF(J1:L1,"")=3)*COUNTIF(N1:P1,"")=3)),"SEND",ISODD((COUNTIF(C1:E1,"")=3)*(COUNTIF(G1:I1,"")=3)*(COUNTIF(J1:L1,"<>")=3)*(COUNTIF(N1:P1,"<>")=3)),"RECEIVE",true,"")

sallux
  • 21
  • 5