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

user_2574595 0
Viewed 38

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.

google-sheets-formula sequence checkbox count sum
2 Answers

try:

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

enter image description here

user_56326290

Awesome question. Though the previous solution does provide the same result, this can also be achieved using a simplified filter() nested inside of a counta():

Example: Spreadsheet with below formula selected

Formula text: =COUNTA(FILTER(A:A, A:A=true, B:B=true, C:C=true, D:D=true, E:E=true))

user_193623160
Related