Do you need to sum random cells from a select range in Google Sheets?

When working on the spreadsheet, there are tasks you will encounter very frequently, such as summing a range of contiguous cells.

Some tasks that may not occur as frequently, but show up now and then, are adding values in cells that are in completely random positions.

This post will demonstrate an easy way to handle the task of summing cells located at random positions so you can do it effortlessly.

Sum Random Cells with Manual Selection

The manual selection method is the first approach we will discuss for summing random cells. To use this method, you will need to manually locate each cell that contains a value you want to sum, and then add up those values.

This sample data has sales information for three products. Assume you want to get the total sales amount for Product A.

To do this, you will find the cells containing the Sales Amount for all Product A items. These are cells C3, C5, C8, and C10.

=C3 + C5 + C8 + C10

After identifying the cells, you can add them up by adding up the cell references without using the SUM function.

=SUM(C3, C5, C8, C10)

You can also use the SUM function to add up the values in the identified cells.

Sum Random Cells with the SORTN and RANDARRAY Functions

Selecting cells at random can be a tedious task and even an impossible one when dealing with large datasets.

However, that doesn’t mean you can’t still sum random cells. With the help of the SORTN and RANDARRAY functions, you can get the sum of random cells.

SORTN Function

The SORTN function in Google Sheets is a function that helps you to sort data in a range or table, and then return a specified number of rows based on the sorting criteria.

This function sorts the data based on one or more columns in ascending or descending order and returns a specified number of rows with the highest or lowest values in those columns.

=SORTN(range, [n], [display_ties_mode], [sort_column], [is_ascending], [sort_column2, …], [is_ascending2, …])

The SORTN function has four parameters or arguments.

• range: the cell range containing the data you want to sort.
• [n]: the number of rows or items to return. This number must be greater than 0. The default value for [n] is one, meaning, by default, the SORTN function will return only one row.
• [display_ties_mode]: this setting controls how the SORTN function handles ties (similar values). You can use either of the following numbers to choose a preferred setting.
  • 0 – to disregard ties and show only the first n rows.
  • 1 – to display the first n rows while including any additional ties with nth row.
  • 2 – to remove duplicates and show n rows only.
  • 3 – to show first n unique rows and all duplicates of these rows.

• [sort_column]: the index number of the column you want to sort, or a range reference to use for sorting. When inputting a range, it’s important to note that it must have the same number of rows as the main range you are sorting.
• [is_ascending]: specifies the sort order of the column using either TRUE or FALSE. TRUE sorts the column in ascending order, while FALSE sorts it in descending order.

All arguments besides the range argument are optional, but if you specify a sort column, you must also indicate the sort order to use.

RANDARRAY Function

The RANDARRAY function in Google Sheets is used to generate an array of random numbers between 0 and 1. This function is particularly useful when you need to create test data or simulate a random process.

=RANDARRAY([rows], [columns])

RANDARRAY uses two optional arguments – [rows] and [columns]. Both arguments are utilized to indicate the range over which the random numbers will be produced.

=SUM(SORTN($C$3:$C$11, 5, 0, RANDARRAY(ROWS($C$3:$C$11)), true))

This formula returns the sum of random top 5 Sales Amount. Here’s a breakdown of how the formula works.

Beginning with the first three arguments in the SORTN function, C$3:$C$11 is the range of cells that will be sorted, 5 indicates the number of rows to return, and 0 tells the SORTN function to disregard ties.

The RANDARRAY(ROWS(C$3:$C$11)) function is utilized in the [sort_column] argument to generate a range of nine random numbers ranging from 0 to 1.

This is possible because the ROWS(C$3:$C$11) portion returns 9, representing the number of rows in the specified range. These random values are then sorted in ascending order and used as the sort column for the range C$3:$C$11.

This argument will cause the sort column to update every time the spreadsheet refreshes, resulting in a dynamic and always-changing outcome.

Sum Random Cells with Custom Function

If you find the previous syntax complex and maybe a little difficult to understand, we created a custom function that does the exact same thing.

To use this script, open your apps script editor by going to the Extensions menu and selecting Apps script.

function shuffleArray(array) {
  var i, j, temp;
  for (i = array.length - 1; i > 0; i--) {
    j = Math.floor(Math.random() * (i + 1));
    temp = array[i];
    array[i] = array[j];
    array[j] = temp;
  }
  return array;
}

function RANDOMSUM(range, n) {
  var rowHeight = range.length;
  var colWidth = range[0].length;
  allItems = [];
  
  
  for (let i = 0; i < rowHeight; i++) {
    for (let j = 0; j < colWidth; j++) {
      allItems.push(range[i][j]);
    };
  };
  
  randSort = shuffleArray(allItems);

  sum = 0

  for (let i = 0; i < randSort.length; i++) {
    if(i < n) {
      sum += randSort[i]
    }
  }

Copy and paste the syntax above and click on the Save and Run commands. After granting the required permissions, go back to your spreadsheet and refresh it.

=RANDOMSUM(C3:C11, 5)

The RANDOMSUM function takes a 2D array as the input range and a positive integer number as the number of elements to sum randomly from the array.

The function flattens the input array into a one-dimensional array, shuffles it, and then sums the first n elements in the shuffled array.

Here’s how the code works.

The function definition starts with the function RANDOMSUM(range, n) { statement, which defines the function’s name and parameters.

Inside the function, the first few lines of code compute the number of rows and columns in the input range array and flatten the array into a one-dimensional array allItems using nested for loops.

The shuffleArray function is then called with allItems as the argument to randomly shuffle the elements in allItems.

The next block of code initializes a variable sum to zero and then iterates over the shuffled allItems array using a for loop. The loop continues until number elements have been summed or all elements in the shuffled array have been considered, whichever comes first.

The if statement inside the loop checks whether the index i is less than n. If it is, then the corresponding element in the shuffled array is added to the running sum.

Finally, the function returns the value of sum. Just like in the SORTN and RANDARRAY function, RANDMOMSUM returns a new result every time it’s called.

Conclusions

Google Sheets offers a variety of methods to help you sum values in different scenarios, including single or multiple columns, and even in random locations.

As summing cells is a frequent task, being proficient in how to sum values in random locations can significantly enhance your spreadsheet skills.

Do you know of other ways to sum random cells in Google Sheets? Let me know in the comment section!