Count and number how many times the same conditions have me

formulasgoogle sheets

I'm trying to see if there is any way to count in Google Sheets how many times conditions in other columns are met. Please see image below. In the example, there are 3 conditions (Car, Color, Status) and they will all be counted as the first case if no conditions are repeated in previous rows. If the same conditions are met in a previous row, the count will go up in one. In the example, only only in the case of Mercedes, red, new is repeated twice, so in the second time it happens, the result will show 2 in the second time.

Best Answer

Use countifs() with three conditions, like this:

=countifs( 
  A$2:A2, A2, 
  B$2:B2, B2, 
  C$2:C2, C2 
)

Related Solutions

Google-sheets – How to count cells that are below a certain date and check if other cells in the current row are empty

I think the question asks for a count of rows where the date is before AY12 and the other columns are blank (not populated). It's simpler to "nest" the IF statements, I think - I'd use this version

=ARRAYFORMULA(SUM(if(Mona!$C$4:$C$400<=$AY12,if(Mona!$B$4:$B$400="",if(Mona!$D$4:$D$400="",if(Mona!$E$4:$E$400="",1))))))

Note that blank cells in column C will also be deemed to be <=AY12 so if those might be blank too then you might want to add a check to ensure that that column is not blank, i.e.

=ARRAYFORMULA(SUM(if(Mona!$C$4:$C$400<=$AY12,if(Mona!$C$4:$C$400<>"",if(Mona!$B$4:$B$400="",if(Mona!$D$4:$D$400="",if(Mona!$E$4:$E$400="",1)))))))

Replace commas with semi-colons if your version requires it

Note that your COUNTIF formula quoted above doesn't need the "Arrayformula" part, can simply be this

=COUNTIF(Mona!$F$4:$F$400;"<="&$AY12)

Google-sheets – How to write a formula to compare two cells and write the answer to another cell

I believe this is what you want. If the comments in the code isn't enough, feel free to ask me for assistance.

function compareCells(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();                   //Get active Spreadsheet
  var listSheet = ss.getSheetByName('Sheet15');                     //Get sheet "Sheet15"
  var dataSheet = ss.getSheetByName('Raw Data');                    //Get sheet "Raw Data"
  var listSheetLastRow = listSheet.getLastRow();                    //Get number of last row with data in "Sheet15"

  var currRow = 2;                                                  //Start looping on row to, since row 1 is header

  for (i = 2; i <= listSheetLastRow; i++){                          //For-loop, looping through all rows with data in "Sheet15"
    var ticker = listSheet.getRange(currRow, 1).getValue();         //Getting value in column A in current row from "Sheet 15"
    dataSheet.getRange(1, 2).setValue(ticker);                      //Setting above value in cell A1 in "Raw Data"
    Utilities.sleep(40000);                                         //Pausing for 40 seconds (40 000 milliseconds)

    var value1 = dataSheet.getRange(7, 2).getValue();               //Getting value from B7 in "Raw Data"
    var value2 = dataSheet.getRange(13, 2).getValue();              //Getting value from B13 in "Raw Data"

    if (value1 > 0.1 && value2 > 0.1){                              //If both values gotten above is greater than 0.1 (10%)
      listSheet.getRange(currRow, 8).setValue('True');              //Set the value of the cell in current row, in the H column to "True"
    }else{                                                          //Else
      listSheet.getRange(currRow, 8).setValue('False');             //Set the value of the cell in current row, in the H column to "False"
    }
    currRow++;                                                      //Go to next row
  }
}

Best Answer

Related Solutions

Google-sheets – How to count cells that are below a certain date and check if other cells in the current row are empty

Google-sheets – How to write a formula to compare two cells and write the answer to another cell

Related Topic