In row D, I would like to count the values of row E (COUNTA) but only up the the current row of the column.
I tried using ARRAYFORMULA and COUNTA and ROW, but from what I’ve read, COUNTA won’t work with ARRAYFORMULA.
What I get with this formula is the total COUNTA of the column returned for each row.
I figured it out! Turns out the solution is an app script. Here’s my code:
function myFunction() {
var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
ss.getRange(“D2”).setFormula(“=IF(ISBLANK($A2),COUNTA(INDIRECT($F2)))”);
var lr = ss.getLastRow();
var downRange = ss.getRange(2, 4, lr-1);
ss.getRange(“D2”).copyTo(downRange);
}
So if anyone is having difficulty getting ARRAYFORMULA to fill in the formula, just insert your formula as it would be in your top row, and change your destination in the getRange argument (change “D2”).
Also, my last row is set to lr-1 because I have a header row. If not, just do lr.
1 Like