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

i'm trying use function 2 conditions - sum if condition true , sum if background color not.

normally i'll use function sumifs here have 1 built-in function , 1 custom function. both of functions work fine separately cannot combine them one.

to "sum if condition true" i've used built-in function sumif , "sum if background color not" i've used function script library.

here link spreadsheet spreadsheet

additional thing need sum different columns ex. values2 same conditions.

using @ritz code i'm trying modify code , have script:

  /**      * @return sum of range b corresponding value in column matches 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 variable "sum" doing concatenation of values doesn't sum it. (the result shown on print screen on cell h10).

i'll grateful clues.

try below script. need pass color , range of both columns parameters. =conditionalcheck("#ffff00","a2:b12")

/**  * @return sum of range b corresponding value in column matches 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; } 

wiki