CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formulaarray-formulas
Kamilah
Kamilah

Reputation: 21

Using COUNTIF with the Array Formula

I'm trying to get a simple COUNTIF formula to combine with the Array Formula in order to count the number of zeros in each row. The original basic formula would look like this:

=COUNTIF($C6:$CD6,"0")

I would want the formula to go down so that it counts the number of zeros in each row.

Please could someone tell me which formula to use? I've tried countless versions with Arrayformula and none of them are working the way I want them to.

Thank you.

EDIT/ADDITION: I've used the formula below to count the zeros in a row.

=ArrayFormula(IF(ISBLANK($A6:$A),,INDEX(MMULT(1*(IF($CH6:$DA="", "×", $CH6:$DA)=0), SEQUENCE(COLUMNS($CH6:$DA), 1, 1, )))))

I have used another formula (below) to sum the amounts in certain columns (which are categorised). =ArrayFormula(IF(ISBLANK($A5:$A),,SUMIF(IF(($C$2:$CD$2)="Alphabet",ROW($C$5:$C)),ROW($C5:$CD),$C5:$CD)))

In addition to this, I've realised it would be helpful to be able to count the zeros in those same columns. That means I would kind of combine the two formulas above.

Is there a way to do this, please?

Upvotes: 1

Views: 1425

Answers (1)

player0
player0

Reputation: 1

try:

=INDEX(MMULT(1*(IF(A1:D4="", "×", A1:D4)=0), SEQUENCE(COLUMNS(A1:D1), 1, 1, )))

enter image description here

Upvotes: 0

Related Questions