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

kaitlynmm569 · Accepted Answer · 2022-07-21 13:20:40Z

1

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

answered Jul 21, 2022 at 13:20

kaitlynmm569

1,7251 gold badge9 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mike Steelson · Accepted Answer · 2022-07-21 15:13:02Z

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

answered Jul 21, 2022 at 12:37

Mike Steelson

15.4k2 gold badges7 silver badges23 bronze badges