Using iF with Array in Google Sheets

Question

I've a table where I use countif and if combination and it is working absolutely fine, but when i try to use it arrayformula in breaks.

Formula:

=IF(COUNTIF(A1:D1,"#N/A")-COUNTIF(A1:D1,"")>0,"Ok","No Ok")

Table :-

Is there any way i can use arrayformula with it, I don't want to drag data everytime data increase

Here is the sheet for your reference :- https://docs.google.com/spreadsheets/d/1DXbsvG8kxqGfNAEWgrvHwIwTsJC2GLX_CG3ol2s0ATo/edit#gid=0

Answer 1

Try

=arrayformula(if(mmult(if(iserror(A1:D),1,0)-iferror(if(A1:D="",1,0),0),sequence(columns(A:D),1,1,0))>0,"Ok","No Ok"))

reference : MMULT

I have added some rows that produce OK

Answer 2

Here is a formula that should work for you:

=arrayformula(if(ArrayFormula(dcount(transpose({row(A1:A),ifna(A1:D,1)}),sequence(rows(A1:A)),{if(,,);if(,,)})-columns(A1:D))>-2, "Ok", "Not Ok"))