Google-sheets – How to sum values of the same row from different columns in a separate sheet with QUERY

With the below script you can build a validator from multiple ranges, in both old and new style Google Spreadsheets and you can select different sheets.

Code

// create menu item
function onOpen() {
  createMenu('Validation', 'Add validation to active cell', 'myValidation');
}

// add validation to active cell
function myValidation() {
  var ss = SpreadsheetApp.getActive(), output = [];

  // create input box or prompt
  var int = getPrompt('How many ranges do you want to combine?', 'Add integer');

  // collect data
  output = getData(1);
  for(var i=1; i<int; i++) {
    output.push.apply(output, getData(i+1));
  }

  // build validation
  var rule = SpreadsheetApp.newDataValidation()
    .requireValueInList(output.sort(sortNumber)).build();
  ss.getActiveCell().setDataValidation(rule);
}

// show prompt and retrieve the corresponding data
function getData(int) {
  var ss = SpreadsheetApp.getActive(), text, values;

  // create input box or prompt
  var text = getPrompt('Range like A1:A4 or Sheet1!A1:A4', 'Range ' + int).split('!');  

  // check if reference to other sheet is made  
  if(text.length > 1) {    
    values = ss.getSheetByName(text[0]).getRange(text[1]).getValues();
  } else {
    values = ss.getRange(text).getValues();
  } 

  // check for entries
  if(values[0].length > 1) {
    throw 'Only single columns are to be used';
    return;
  } 

  // return the data
  return values;    
}

function createMenu(menuName, itemName, funcName) {
  try {
    SpreadsheetApp.getUi()
      .createMenu(menuName).addItem(itemName, funcName).addToUi();
  } catch(e) {
    SpreadsheetApp.getActive()
      .addMenu(menuName, [{name: itemName, functionName: funcName}]);    
  }
}

function getPrompt(title, header) {
  // create input box or prompt
  try {
    return SpreadsheetApp.getUi().prompt(title, header, app.ButtonSet.OK).getResponseText();
  } catch(e) {
    return Browser.inputBox(title, header, Browser.Buttons.OK);
  }
}

// http://www.devcurry.com/2010/08/sort-numeric-array-in-javascript.html
function sortNumber(num1, num2) {
  return num1 - num2;
}

Example

I've created an example file for you: validator from two ranges
Add the script under Tools>Script editor and press the bug button to activate the authentication.

In your particular example, you can use a SUMPRODUCT formula.

=SUMPRODUCT((A1:A5*(A1:A5>=B1:B5)*(C1:C5="Yes"))+(B1:B5*(B1:B5>A1:A5)*(C1:C5="Yes")))

This approach may be clumsy in your real world example, though. If your real data is more complicated, post a snippet that shows that complication and I can take a more advanced crack at it.