# How to select random cells in a row that add up to 300, and count how many it took

I have a column that contains a bunch of random integers, I would like to select a number of those cells until they add up to, or just over 300, then output the count of how many rows it took to make 300.

``````Col 1 | Col 2
-------------
44    |
32    |
78    |
12    |
2     |
..    |
23    |
2     |
``````

Update: ended up writing this, does what I need.

``````function getTotalWeeks( runCount ) {

// Make is sleep
Utilities.sleep(1000)

// Do we have runCount?
var runCount = (runCount === undefined ? 999 : runCount)

// Get the vars
var total_stories_r = vars.getRange('E7');
var total_stories = total_stories_r.getValue()

// Get the week sims
var possible_velocity = monte.getRange('A2:A');
var data = possible_velocity.getValues();

var weeks_arr = []

// Print for the run count
for (var ii = 0; ii < runCount; ii++) {
var total = 0
for(var weeks = 0; weeks < data.length; weeks++)
{
var element = data[Math.floor(Math.random() * data.length)]; // Random elent from Array
total = +total + +element // + unary operators to convert strings to ints
if (total >= total_stories) {
var bollocks = []
bollocks.push(weeks)
weeks_arr.push(bollocks)
break;
}
}
}

// Put it all back
var range = monte.getRange("B1");
range.setValues([["Number of weeks"]])

var range = monte.getRange("B2:B1000");
range.setValues(weeks_arr);

}
``````
• You'll need to write a script May 1 '18 at 15:27
• You can submit the script as an answer and accept it. May 3 '18 at 8:54

``````=SUMPRODUCT(SUMIF(ROW(A2:A),"<="&ROW(A2:A),A2:A)<300)+1
``````
• SUMIF to cumulatively add A2:A
• SUMPRODUCT to add TRUE's,IF SUM<300
• A2:A is added in first row to last row order. If you want to add randomly/in any other order, You'll need to write a custom script.

Update: ended up writing this, does what I need.

``````function getTotalWeeks( runCount ) {

// Make is sleep
Utilities.sleep(1000)

// Do we have runCount?
var runCount = (runCount === undefined ? 999 : runCount)

// Get the vars
var total_stories_r = vars.getRange('E7');
var total_stories = total_stories_r.getValue()

// Get the week sims
var possible_velocity = monte.getRange('A2:A');
var data = possible_velocity.getValues();

var weeks_arr = []

// Print for the run count
for (var ii = 0; ii < runCount; ii++) {
var total = 0
for(var weeks = 0; weeks < data.length; weeks++)
{
var element = data[Math.floor(Math.random() * data.length)]; // Random elent from Array
total = +total + +element // + unary operators to convert strings to ints
if (total >= total_stories) {
var bollocks = []
bollocks.push(weeks)
weeks_arr.push(bollocks)
break;
}
}
}

// Put it all back
var range = monte.getRange("B1");
range.setValues([["Number of weeks"]])

var range = monte.getRange("B2:B1000");
range.setValues(weeks_arr);

}
``````