How to get the size of a merged cell in Google Sheets formula-CodePudding

I have a spreadsheet where some columns are progressively merged to represent the balance of a period. Roughly like this:

(don't mind the actual values, these are random just to illustrate)

It is easy enough to just use simple formulas to refer to the ranges to the left when you are looking at them and know, for instance, that this week is C16:C22, next week is C23:C29 and so on. Weeks are relatively simple as they are regular, but months are a bit more complex, yet still just as doable. It is toilsome, though.

What I would really like to do though, would be to just get the size of the merged cell, for instance, if there was a way to write a formula like CELL('rows') that would tell me how many rows it occupies.

Unfortunately I've only managed to find a ROW() formula function that only tells the first row of the current (merged) cell, and a ROWS() that requires that I pass it a range, which is what I'm trying to obtain in the first place. Once I have the cell size in rows, I can infer its data range to the left and won't have to manually edit the formulae for each week and month. Even if I still need to do the merging, that will save a ton of work.

I would prefer to stick with formulae only, but if the solution lies in a script, so be it.

CodePudding user response：

counting merged cells is possible only with script.

but there is an alternative... for week count you can use:

=ARRAYFORMULA(IF(A2:A="";;ISOWEEKNUM(A2:A)))

then to turn it into a count like:

=ARRAYFORMULA(IF(A2:A="";;COUNTIFS(
 ISOWEEKNUM(A2:A); ISOWEEKNUM(A2:A); ROW(A2:A); "<="&ROW(A2:A))))

respectively for a month, you can do:

=ARRAYFORMULA(IF(A2:A="";;MONTH(A2:A)))

CodePudding user response：

I could not get the desired results with a formula like @player0 but as an alternative, you can also try this script.

Once you run the script, it will write on column F the number of rows and the range of the merged cells from column E. You can change ranges in the script to test it with other columns that have merged cells.

function numRows(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Página1");
  var range = sheet.getRange("E2:E")
  if( range.isPartOfMerge() ) {
    var range = range.getMergedRanges();

    for (i=0; i<range.length;i  ){
      var numRows = range[i].getNumRows();
      var ranRows = range[i].getA1Notation();
      var lastRow = range[i].getLastRow();

      Logger.log("Number of rows in range "  ranRows   " is: "   numRows)
      sheet.getRange("F" lastRow).setValue("Range: " ranRows "\n"  "NumRows: " numRows)
    }
  }
  else {
  }
}

Let me know if you have any questions.