Byrow formula with reference to last row and last column

Question

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.

Answer 1

You can also try this non-lambda formula:

=ArrayFormula(LET(rowSize,IFERROR(MATCH(,0/($A$2:$A<>"")),0),
                  headers,$C$1:$1,
                  colA,OFFSET($A$1,1,0,rowSize),
                  data,OFFSET(headers,1,0,rowSize),
                  rowList,SEQUENCE(rowSize),
                  applyRows,IF(SEQUENCE(1,COLUMNS(headers)),rowList,),
                  countSummary,IF(colA<>"",COUNTIFS(data,"<>",applyRows,rowList),),
                  countSummary))

Answer 2

Counting the non-blanks cells

Based on your additional provided information. You can try this formula.

=BYROW(C1:ZZZ, LAMBDA(r, IF(A1:A <> "", IF(COUNTA(r) = 0, "", COUNTA(r)), "")))

Output:

If you are looking for an alternative approach, this also works. just to give you another option.

=ARRAY_CONSTRAIN(LET(x, A1:A, y, C1:ZZZ, BYROW(y, LAMBDA(r,IF(ISBLANK(x),"", COUNTA(r))))),COUNTA(A1:A),1)

Output:

UPDATE: If counta is not ideal for your use case, you can use this instead for a simple implementation:

=BYROW(C1:ZZZ, LAMBDA(r, IF(A1:A <> "", if(count(r,">""") = 0, "", COUNT(r,">""")), "")))

NOTE: Please adjust the range accordingly.

Reference:

• Counta
• Array_constrain
• Isblank

Answer 3

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(Σ,">"""))))

• The range will be dynamically restricted to the last non-blank value found in Column_A(row purpose) and last value in 1:1 header (column purpose). In the screenshot the dynamic range that will be taken into consideration(as of that moment) is highlighted in orange

Answer 4

Assuming the empty cells from C3 onwards are actual blank values and not empty strings "", you can use:

=ARRAYFORMULA(IF(A3:A="",,BYROW(C3:ZZZ,LAMBDA(r,COUNTA(r)))))

If you have formulas in C3:ZZZ that return an empty string "", change them to return a blank value. (i.e. IF(cond,"",else) -> IF(cond,,else))

Answer 5

I managed to figure this out. Here is what I ended up using for those interested.

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

I'm not sure if there is a cleaner or more efficient alternative but this seems to accomplish what I needed. I will refrain from choosing my own answer until I hear from others.