Google-sheets – Count consecutive columns with the same values

formulasgoogle sheetsgoogle-apps-scriptgoogle-sheets-arrayformulavlookup

Before I start, here is a link to the sheet example:

Essentially what I want to do is count the number of times the value in C5 occurs consecutively in its first occurrence.

In the example on Sheet1, the value would be 5 because Hawaii occurs
5 in a row before changing values.
In the example on Sheet2, the value would be 2 because of Air Force
occurs 2 in a row before changing values

I count using Countif() but I only want the consecutive count for the first instance.

Any tips would be appreciated.

Best Answer

paste this into F5 (for example):

=MAX(ArrayFormula(IF(C5:C12=A2,
    MMULT(N(ROW(C5:C12)>=TRANSPOSE(ROW(C5:C12))),N(C5:C12=A2))
    -
    HLOOKUP(0,
      MMULT(N(ROW(C5:C12)>TRANSPOSE(ROW(C5:C12))),N(C5:C12=A2)),
      MATCH(VLOOKUP(ROW(C5:C12),IF(N(C5:C12<>C4:C11),ROW(C5:C12),),1,TRUE),
            VLOOKUP(ROW(C5:C12),IF(N(C5:C12<>C4:C11),ROW(C5:C12),),1,TRUE),
            0),FALSE),)))

or sentence:

=A2&" won "&MAX(ArrayFormula(IF(C5:C12=A2,
    MMULT(N(ROW(C5:C12)>=TRANSPOSE(ROW(C5:C12))),N(C5:C12=A2))
    -
    HLOOKUP(0,
      MMULT(N(ROW(C5:C12)>TRANSPOSE(ROW(C5:C12))),N(C5:C12=A2)),
      MATCH(VLOOKUP(ROW(C5:C12),IF(N(C5:C12<>C4:C11),ROW(C5:C12),),1,TRUE),
            VLOOKUP(ROW(C5:C12),IF(N(C5:C12<>C4:C11),ROW(C5:C12),),1,TRUE),
            0),FALSE),)))&" times in a row"

Related Solutions

Google Sheets – How to Refresh a Cell

Add the following line of code:

SpreadsheetApp.flush()

See reference: flush

Google-sheets – Getting values from other sheets with a custom function

Custom functions are re-run when their parameters change. For example, if you have =myfcn("sheetname", 1, 1, 1, 1) and change some of parameters here, the function will run again. But if you go and change the content of cell sheetname!A1, the function will not re-run, since there is no indication in the spreadsheet that its output depends on sheetname!A1.

Alternative

To get all formulas from a sheet into another sheet as text, use a script function like this:

function copyFormulas() {
  var sheet = SpreadsheetApp.getActiveSheet();
  if (sheet.getName() == 'Watched Sheet') {   
    var data = sheet.getDataRange();
    var target = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Formulas');
    target.getRange(1, 1, data.getHeight(), data.getWidth()).setValues(data.getFormulas());
  }   
}

The function takes formulas from 'Watched Sheet' and puts them into 'Formulas' sheet as text. This script can run periodically, or on every edit, or on every change (including adding rows, etc). See triggers.

Best Answer

Related Solutions

Google Sheets – How to Refresh a Cell

Google-sheets – Getting values from other sheets with a custom function

Alternative

Related Topic