Google Spreadsheet return a subset given complex criteria

Question:

Here is my sample sheet:

Given the example data in this sheet, I want to return a subset automatically that meets the following criteria:

* 4 total rows

* 1 row where color=green

* 1 row where color=red

* 2 rows where color=blue

* The COMBINED value of the PRICE column of the four returned rows cannot exceed a sum of 30

* The returned set should provide the highest possible COMBINED value of the POINTS column of the four returned rows. 

In short, I would like to find the four records in the entire table that result in the highest possible combined POINTS value, without exceeding a combined PRICE value of 30.

Help is greatly appreciated!

Solution:

Insert the following script code in the script code of your spreadsheet:

////////////////////////////////////////////////////////

function subset(v) {
  //var ss = SpreadsheetApp.getActiveSpreadsheet();
  //var s = ss.getSheetByName('Sheet1');
  //var r = s.getRange('A:E');
  //var v = r.getValues();
  var lastrow = getLastPopulatedRow(v);
  var green = new Array();
  var red = new Array();
  var blue = new Array();
  
  for(var i=0;i<lastrow;i++) {
    if(v[i][2]=='green')
      green.push(v[i]);
    else if(v[i][2]=='red')
      red.push(v[i]);
    else if(v[i][2]=='blue')
      blue.push(v[i]);
  }
  
  var combinations = new Array();
  var c=0;
  var total = new Array();
  
  for(var w=0;w<green.length;w++) {
    for(var x=0;x<red.length;x++) {
      for(var y=0;y<blue.length-1;y++) {
        for(var z=y+1;z<blue.length;z++) {
          combinations[c] = new Array();
          combinations[c].push(green[w]);
          combinations[c].push(red[x]);
          combinations[c].push(blue[y]);
          combinations[c].push(blue[z]);
          total.push([c,(green[w][3]+red[x][3]+blue[y][3]+blue[z][3]),(green[w][4]+red[x][4]+blue[y][4]+blue[z][4])]);
          c=c+1;
        }
      }
    }
  }
  var total = total.sort(function(a,b) { return a[1] - b[1]; });
  //Logger.log(total);
  for(var j=total.length-1;j>=0;j--)
    if(total[j][2]<=30) break;
  
  return combinations[total[j][0]];
};

function getLastPopulatedRow(data) {
  for (var i=data.length-1;i>=0;i--)
    for (var j=0;j<data[0].length;j++)
      if (data[i][j]) return i+1;
  return 0;
};

////////////////////////////////////////////////////////

Now you can use the following custom function in your spreadsheet:
=subset(A2:E)

Following is the screenshot of the spreadsheet, showing the desired subset in range G2:K5
I have inserted the custom formula in cell G2.