Can I COUNTIF across multiple columns in an array formula?-CodePudding

Seems simple to me..

I want to do something along the lines of =Countif(A2:X , "Yes")

I would wrap this in an arrayformula with a simple IF(A2:A = "" ,"" .....

I want it to bring back the number of "Yes" per row

So if row 2 has 12 yes' then bring back 12, row 3 has 7 so 7 etc

This has worked to an extent but brings back the count across the entire range rather than split by row

CodePudding user response：

Try:

=ArrayFormula(IF(A2:A="",,MMult(N(A2:X="Yes"),Sequence(Columns(A2:X))^0)))

=ArrayFormula(IF(A2:A="",,Len(RegexReplace(RegexReplace(Transpose(Query(Transpose(A2:X),,9^9)),"(?i)\bYes\b","~"),"[^~]",))))

=ArrayFormula(IF(A2:A="",,Len(Substitute(Transpose(Query(Transpose(IF(A2:X<>"Yes",,"~")),,9^9))," ",))))

Update

If I wanted to ammend this formula to do if A2:X = No AND A1:X1 contains, for instance (AG) how would I do this?

=ArrayFormula(IF(A2:A="",,MMult((A2:X="No")*RegexMatch(A1:X1,"AG"),Sequence(Columns(A2:X))^0)))

CodePudding user response：

Though it is a bit tedious, couldn't you do this:

=COUNTIF(A2:A, "Yes")   COUNTIF(B2:B, "Yes")   COUNTIF(C2:C, "Yes")   COUNTIF(D2:D, "Yes")   COUNTIF(E2:E, "Yes")   COUNTIF(F2:F, "Yes")   COUNTIF(G2:G, "Yes")   COUNTIF(H2:H, "Yes")   COUNTIF(I2:I, "Yes")   COUNTIF(J2:J, "Yes")   COUNTIF(K2:K, "Yes")   COUNTIF(L2:L, "Yes")   COUNTIF(M2:M, "Yes")   COUNTIF(N2:N, "Yes")   COUNTIF(O2:O, "Yes")   COUNTIF(P2:P, "Yes")   COUNTIF(Q2:Q, "Yes")   COUNTIF(R2:R, "Yes")   COUNTIF(S2:S, "Yes")   COUNTIF(T2:T, "Yes")   COUNTIF(U2:U, "Yes")   COUNTIF(V2:V, "Yes")   COUNTIF(W2:W, "Yes")   COUNTIF(X2:X, "Yes")