1

How can I write a formula that will count the number rows that meet a condition in one of the columns?

I found this to work in Excel:

=SUM(N(MMULT(N(N2:DD14>2.5),ROW(INDEX(A:A,1):INDEX(A:A,COLUMNS(N2:DD14)))^0)>0))

But how can I get something like this to work in Google Sheets?

0

1 Answer 1

0

Here is an implementation of the same mmult approach in Google Sheets. Your problem may have been because of the lack of arrayformula.

=countif(arrayformula(mmult(N(A1:F9 > 10), transpose(iferror(A1:F1/0, 1)))), ">0")

Here A1:F9 is the array being processed, and ">10" is the condition. So, N(A1:F9 > 10) returns a 0-1 array of the same size. It is summed by rows using mmult. The second argument is a column of 1s; I use iferror(A1:F1/0, 1) to generate an array of 1s of the same size as A1:F1.

Finally, countif counts positive entries of the product, i.e., the rows with at least one positive number.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.