CODEWITHSUNDEEP
google-sheetsarray-formulasifs
sallux
sallux

Reputation: 21

arrayformula with ifs, isblank and AND/OR in google sheets

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.

Upvotes: 2

Views: 1318

Answers (2)

sallux
sallux

Reputation: 21

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,"")

Upvotes: 0

N.O.Davis
N.O.Davis

Reputation: 513

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","")))

Upvotes: 1

Related Questions