Sum if condition is true and background color is not - Google Spreadsheet

Question

I'm trying to use a function with two conditions - Sum if condition is true and sum if background color is not.

Normally I'll use function SUMIFS but here I have one built-in function and one custom function. Both of this functions work fine separately but I cannot combine them together as one.

To "Sum if condition is true" I've used built-in function SUMIF and for "Sum if background color is not" I've used a function from Script Library.

Here is a link to my spreadsheet Spreadsheet

Additional thing is that I need to sum different columns ex. Values2 with same conditions.

Using @Ritz code I'm trying to modify his code and I have this script:

  /**
     * @return The sum of range B where the corresponding value in Column A matches the color
     * @customfunction
     */

function conditionalCheck(color,rangeCondition, rangeSum, criteria){
  var condition = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange(2,rangeCondition,11).getValues();
  var val = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange(2,rangeSum,11).getValues();
  var bg = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange(2,rangeSum,11).getBackgrounds();
  var sum = 0;
  for(var i=0;i<val.length;i++){
     if(condition[i] == criteria && bg[i] != color){
       sum += val[i];
     }
  }
  return sum;
}

But result is that variable "sum" doing concatenation of the values but doesn't sum it. (The result is shown on print screen on cell H10).

I'll be grateful for any clues.

.getValues() returns e.g [[1,2],[3,4]] so inside for loop you need mention the second index — Ritesh Nair

Ritesh Nair Ritesh Nair · Accepted Answer · 2017-08-22T08:02:54

Try the below script. You need to pass the color and range of both the columns as parameters. =conditionalCheck("#ffff00","A2:B12")

/**
 * @return The sum of range B where the corresponding value in Column A matches the color
 * @customfunction
 */
function conditionalCheck(color,range){
  var val = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange(range).getValues();
  var bg = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange(range).getBackgrounds();
  var sum = 0;
  for(var i=0;i<val.length;i++){
     if(val[i][0] == "A" && bg[i][1] == color){
       sum = sum + val[i][1]
     }
  }
  return sum;
}

Sum if condition is true and background color is not - Google Spreadsheet

1 Answers