ARRAYFORMULA and COUNTIF

Question

I have a formula:

=IFERROR(COUNTIF(C14:V14,"I")/ COUNTIF(C14:V14,"<>"),)

to compute a percentage of I inputs divided by <> (all other inputs) and I need to omit blank cells. The IFERROR formula just keeps the cell blank until data is entered in the corresponding row.

I want this formula to extend to every new row of a column. I have used ARRAYFORMULA before to automatically extend the formula to new rows but I can't figure out how to use it (or something else?) in this case.

Answer 1

assuming your data starts in row 14, try:

=ArrayFormula(IF(LEN(C14:C),COUNTIF(IF(C14:O="I",ROW(C14:C)),ROW(C14:C))/COUNTIF(IF(C14:O<>"",ROW(C14:C)),ROW(C14:C)),))

and format the column containing (the output of) this formula as % (via the 123-button).