Google Sheets Formula – Why Formula Doesn’t Match Total in Yahoo

I have found a way to do this, using the scripting functionality that Google Spreadsheet provides.

Here's how to do it:

• open your spreadsheet
• in the menu, go to Tools -> Script Editor...; this will open a new window that allows you to enter code
• copy the code below
• paste the code in the Script Editor window and press CTRL+S to save it
• close the Script editor windows and go back to the spreadsheet

Code:

function getBackgroundColor(rangeSpecification) {
  var sheet = SpreadsheetApp.getActiveSheet();
  var cell = sheet.getRange(rangeSpecification);
  return cell.getBackground();
}

function sumWhereBackgroundColorIs(color, rangeSpecification) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var range = sheet.getRange(rangeSpecification);
  var x = 0;
  for (var i = 1; i <= range.getNumRows(); i++) {
    for (var j = 1; j <= range.getNumColumns(); j++) {
      var cell = range.getCell(i, j);
      if(cell.getBackground() == color)
        x += parseFloat(cell.getValue());
    }
  }
  return x;
}

function countCellsWithBackgroundColor(color, rangeSpecification) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var range = sheet.getRange(rangeSpecification);
  var x = 0;
  for (var i = 1; i <= range.getNumRows(); i++) {
    for (var j = 1; j <= range.getNumColumns(); j++) {
      var cell = range.getCell(i, j);
      if(cell.getBackground() == color)
        x++;
    }
  }
  return x;
}

After following the above steps, you have three more functions available in the spreadsheet:

• countCellsWithBackgroundColor(<color>, <range specification>)
• sumWhereBackgroundColorIs(<color>, <range specification>)
• getBackgroundColor(<cell specification>)

Please note that <range specification> and <cell specification> are expressed in A1 notation, and must be enclosed in quotes.

For example, to get the count of all the cells in the range B2:F13 that have the background color set to white, you should enter the following formula:

=countCellsWithBackgroundColor("white", "B2:F13")

and to compute the sum of the same cells, use the formula:

=sumWhereBackgroundColorIs("white", "B2:F13")

Some cells may not have the background set to a color such as 'white', 'gray', but a RGB color like #6fa8dc. You cannot guess what the color is, so if you want to find out the color for a cell (for example, B9), you should enter this formula in a cell:

=getBackgroundColor("B9")

and afterwards use this value as a parameter to the two functions above.

It appears that if a cell's background color is a "theme color," getBackground() incorrectly always returns #ffffff. I haven't found a way around this other than to use a color from the standard set.

Again, note the quotes in all above examples; without them, the formulas would not work.

Your task is hopeless because when only importing a single column there is no second column. However querying say Sheet1!A2:B3000 instead will still not work with COL2 because that is case sensitive, just like column b selection does not work in a query in place of B. Maybe try:

=QUERY(IMPORTRANGE("key","Sheet1!A2:B3000"),"SELECT * WHERE Col2='JB'")