google-sheetsgoogle-sheets-formula

Byrow formula with reference to last row and last column

I have a formula currently that counts the number of non-blank cells for columns C and beyond.

=BYROW(C3:3,LAMBDA(x,COUNTIFS(x,">""")))

This works for a single cell. What I would like to do is to have this formula calculate for any rows below where there is a value in column A. For example, if column A is static at 100 rows I could do the following...

=BYROW(C3:100,LAMBDA(x,COUNTIFS(x,">""")))

This would work to populate a calculation for all cells up to row 100. What if it isn't static though? What if column A is dynamic? How can I replace 100 with a dynamic last row number?

Edit: I've gotten a little closer with the following formula...

=BYROW(INDIRECT("C3:"&ROWS(A:A)),LAMBDA(x,COUNTIFS(x,">""")))

The issue I have with this is that it will continue to provide a value 0 for those cells that do not have a value in column A.

Here is an example of what my table looks like...

Column A Column B Column D Column E Column F Column G Column H Column I
One Formula Here 10/01/24
Two 10/01/24 10/01/24
Three 10/01/24 10/01/24
Four 10/01/24

And what the result should look like...

Column A Column B Column D Column E Column F Column G Column H Column I
One 1 10/01/24
Two 2 10/01/24 10/01/24
Three 2 10/01/24 10/01/24
Four 1 10/01/24

Note, I want 1 formula to be inserted in B2. Number of rows is dynamic. Number of columns is also dynamic.

Solution

  • Here's a generalized approach which you may adapt accordingly:

    =byrow(indirect("C2:"&index(address(match(,0/(A:A<>"")),match(,0/(1:1<>""))))),lambda(Σ,if(index(A:A,row(Σ))="",,countif(Σ,">"""))))

    enter image description here