Search code examples
checkboxcountsumgoogle-sheets-formulasequence

Is there a single-cell formula to count the total number of rows that are completely filled with a value?

I'm looking for an elegant solution to remove the helper column G such that I have a formula in located in a single cell which counts the total number of rows completely filled with a uniform value (in this case TRUE). In the example image below, this would be 2 as displayed in cell I1.

enter image description here

Currently the helper column G is successfully determining the state of if a row is completely filled with a uniform value on a row-by-row basis. This helper column is then used to count the number of true values from column G to provide the final answer in cell I1.

Current Formulae:

G -> =IF(COUNT('Sheet1'!A1:E1)=0, ,(ArrayFormula(SUM(IF({A1:E1}=TRUE,1,0)))=COUNT('Sheet1'!A1:E1)))

I -> =COUNTIF(G1:G5,TRUE)

I'm assuming there is some form of array function I can use to condense column G into the single cell that forms the final answer I currently have in cell I1, without having to use a helper column; however I'm struggling to find or apply it correctly.

Solution

  • try:

    =INDEX(COUNTIF(MMULT(A1:E5*1, SEQUENCE(COLUMNS(A1:E5), 1, 1, )), COLUMNS(A1:E5)))

    enter image description here