CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Heiny002
Heiny002

Reputation: 11

ARRAYFORMULA and COUNTA to count only up to current row

Here's an example of my sheet

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 need to automatically apply the formula to the rows (and new rows) so I had intended to use ARRAYFORMULA. However, I was also looking into QUERY functions as well to no avail. I think my solution might be in scripts, but I am not very confident with them yet.

Upvotes: 1

Views: 792

Answers (3)

TheMaster
TheMaster

Reputation: 50462

D1:

 =ARRAYFORMULA({"What I Want";
  IF(ISBLANK(E2:LOOKUP(2,1/(E:E<>""),E:E)),,
    COUNTIFS(SEQUENCE(LOOKUP(2,1/(E:E<>""),SEQUENCE(2^20))-1),
        "<="&SEQUENCE(LOOKUP(2,1/(E:E<>""),SEQUENCE(2^20))-1),
    E2:LOOKUP(2,1/(E:E<>""),E:E),
        "<>"
    )
  )
})

Notes:

From the inside,

  • LOOKUP(2,1/(E:E<>""),E:E) - LOOKUP provides the last empty range1
  • E2:LOOKUP(..) provides a range: E2:E11 in this case. This syntax can be used to create dynamic ranges
  • LOOKUP(...,SEQUENCE(2^20)) provides last empty row number upto 2^20(~36k) rows
  • SEQUENCE(LOOKUP(...)) provides a sequence of numbers from 1 to last row.
  • SEQUENCE(), "<="&SEQUENCE() inside COUNTIFS provides the logic for running total. First sequence creates a ascending number array(like 1,2,3...). Second sequence provides a ascending criteria array( like <=1,<=2,<=3,...). Each number in the number array is compared against all in the criteria array. For ex, When comparing 2, two criterias are satisfied, (<=1 and <=2). So, for row 2, the count is 2(the count of all previous rows)
  • COUNT rows IF the above condition is satisfied and E:E is not empty ("<>")
  • Empy the blanks with IF(ISBLANK)

Upvotes: 0

Heiny002
Heiny002

Reputation: 11

I figured it out. Turns out, I was right about the solution being 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);

}

Upvotes: 0

player0
player0

Reputation: 1

try like this:

=COUNTA(IFERROR(INDIRECT("E1:E"&ROW())))

Upvotes: 0

Related Questions