How to Sum Random Cells in Excel

Get FREE Advanced Excel Exercises with Solutions!

In this free Excel tutorial, you will learn how to sum random cells in Excel. Summing random cells is a frequent need when working with Excel. It is very much possible that all our required values will not be oriented serially. In that case, we have to sum those random cells in Excel.

You will also learn to sum random cells across multiple worksheets from this tutorial. Moreover, You will learn to generate random values in cells and adjust them to get a certain value in Excel.

For example, we want to calculate the Total Sales of the Sales Representative Abigail from the Sales Report shown below. You see in the image, that we have done this with multiple methods.

Overview of How to Sum Random Cells in Excel

Summing random cells in Excel can be done using Arithmetic calculation formulas; using the SUM function; using Excel AutoSum feature; using Excel Subtotal tool; applying Named Ranges then summing, etc.

Note: We have used Office 365 when preparing this article. However, you can apply the below methods in all versions available in Excel.


1. Using Simple Arithmetic Calculations to Sum Random Cells in Excel

In this method, we will use simple and basic arithmetic calculation technique to sum random cells in Excel.

Here, we have a dataset of January sales containing Date, Sales Rep, and Sales columns. So, we have sales amounts for sales representatives with their respective dates of sale. Now, we want to sum up the total January sales of sales representative Abigail. Her sales amount values are written randomly here. So we have to sum those random cells to achieve our result.

January Sales Data

Follow the steps below to accomplish this result.

Steps:

  • Say, you want to show your result in cell F9. To do this, select cell F9 >> write an equal (=) sign >> click on cells that contain the sales amount of Abigail and separate each of those values by a plus (+) sign.
  • So, the formula will look like:
=D5+D9+D10+D14+D16

Using Simple Arithmetic Formula to Sum Random Cells

  • Press Enter. And, you will see your desired result as $31409.15 as this is the total January sales of Abigail found by summing up all the random cells containing sales of Abigail.

Sum of Random Cells with Arithmetic Formula


2. Applying SUM Function to Sum Non-Contiguous Cells in Excel

In this example, we will use the SUM function to sum random cells of Abigail’s sales. Follow the steps below.

Steps:

  • To get the result in cell F9, select the cell and insert the SUM function.
  • Inside the SUM function, reference the cells containing the sales amount of Abigail. Separate each cell reference with a comma (,). So, the formula will look like:
=SUM(D5,D9,D10,D14,D16)

Using SUM Function to Sum Random Cells

  • Press Enter and you will get your desired result.

Sum of Random Cells with SUM Function


3. Using Excel AutoSum Feature

We can sum random cells in Excel with the Excel AutoSum feature too. Go through the steps below to calculate total sales of Abigail through this method.

Steps:

  • Select the cell where you want your sum result (cell F9 here) >> Go to Home tab >> Editing group >> You will find the AutoSum option.

Accessing AutoSum Feature

  • Clicking on AutoSum option will automatically insert the SUM function in the formula bar.
  • Reference the cells containing Abigail’s sales like before with separated commas. This will result in the following formula:
=SUM(D5,D9,D10,D14,D16)

Using AutoSum Feature to Sum Random Cells

  • Press Enter. You will get your desired sum result of random cells.

Sum of Random Cells with AutoSum Feature


4. Applying Named Ranges to Sum Random Cells in Excel

We can also create a named range with selected random cells and use that named range in the SUM function to get our sum. Follow the steps below to do this.

Steps:

  • Hold Ctrl key on your keyboard >> Select cells that contain Abigail’s sales >> Write “Abigail” in the Excel Name Box.

Naming Range of Random Cells

  • Pressing Enter will convert those cells into a named range “Abigail”.
  • Select your desired output cell (Cell F9 here) and insert the following formula.
=SUM(Abigail)

 Sum Formula with Named Range

  • Press Enter and you will get your desired result which is $31409.15.

Sum of Random Cells with Named Range


5. Using Insert Function Button to Sum Random Cells in Excel

There is a built-in Insert Function button in Excel which can be used to insert formulas. We can sum random cells through this button too. Follow the steps below to follow this approach.

Steps:

  • Select the desired output cell (Cell F9 here) >> You will find an fx button beside the formula bar.

Accessing Insert Function Button

  • Click on the fx button and you will get an Insert Function dialogue box. Select SUM from the options available in Select a function: options.

Insert Function Dialogue Box

  • Click on the OK button and you will get another window named Function Arguments.
  • In the Number1 text box, insert the first cell reference of Abigail’s sales amount.
  • Similarly, insert all the cell references of Abigail’s sales one by one in each number text box.

Function Arguments Dialogue Box

  • Click on the OK button and you will get your desired sum result.

Sum of Random Cells with Insert Function Button


6. Using Subtotal Tool to Sum Non-Contiguous Cells in Excel

Using Excel’s Subtotal tool, we can group cells depending on several conditions and also find the sum of those grouped cells. In this example, we will follow this approach. Go through the steps below to sum random cells containing sales of Abigail.

Steps:

  • To use the Subtotal tool, we need to distinguish the cells containing Abigail’s sales in a single column.
  • To do this, create a new column named Abigail Sales and insert the following formula in cell E5.
=IF(C5="Abigail",D5,"")

Using IF Function to Find Only Abigail's Sales

  • Press Enter and you will get the sales amount in cell E5 if the sales representative is Abigail, otherwise, you will find a blank cell.
  • Place your cursor in the bottom right position of cell E5 and you will find Excel’s fill handle.

Using Fill Handle to Copy Formula

  • Drag the fill handle below and you will get all the sales amount of Abigail in column E.
  • Select cells D4:E19 >> Go to Data tab >> Outline group >> You will find the Subtotal tool.

Accessing Subtotal Tool

  • Click on the Subtotal tool and you will get a Subtotal dialogue box.
  • Choose Abigail Sales in At each change in: dropdown >> Choose Sum in Use function: dropdown >> Select Abigail Sales checkbox in Add Subtotal to: options.

Subtotal Dialogue Box

  • Click on the OK button and you will see that there will be several groups each created after Abigail’s sales is found and there will be a total sum after each group and a Grand Total after summing all totals which is our desired value.

Subtotal of Abigail's Sales


7. Applying SUMIF Function to Sum Random Cells Based on Criteria in Excel

When summing random cells, if we work with specific conditions, then we can apply the SUMIF function to sum those random cells in Excel. Go through the steps below to apply this.

Steps:

  • To find the total sales of Abigail in cell F9, select the cell >> Insert the following formula.
=SUMIF(C5:C19,"Abigail",D5:D19)

SUMIF Function to Sum Random Cells

  • Press Enter and you will get the total sales of Abigail.

Sum of Random Cells Based on Criteria


8. Summing Random Cells Across Multiple Sheets in Excel

Now, if we want to sum random cells that are in different worksheets, then we can use the SUM function similarly to before with different worksheets’ references.

Say, we have our initial January sales data and now also February sales data. We want to get total sales of Abigail for the months of January and February.

February Sales Data

Steps:

  • To get the result in cell F9, select the cell and insert the following formula.
=SUM('January Data'!D5,'January Data'!D9,'January Data'!D10,'January Data'!D14,'January Data'!D16,'February Data'!D9,'February Data'!D12,'February Data'!D15)

Formula to Sum Random Cells Across Multiple Sheets

  • In the formula, all the random cells of Abigail’s sales from January Data sheet and February Data sheet are referenced.
  • Pressing Enter will find the sum of Abigail’s sales for January and February.

Sum of Random Cells Across Multiple Sheets


9. Summing Random Cells to Get a Certain Value in Excel

Now, say, we have a specific target value and we want to achieve that target value with randomly generated values. To generate, adjust, and sum random cells to get a certain value, follow the steps below.

Steps:

  • Insert your target value (50000 here) in the desired cell (cell E9 here). To generate a random value in cell B5, insert the following formula with the RAND function.
=RAND()

Generating Random Value

  • For all the other randomly generated values, place your cursor in the bottom right position of cell B5 and you will find the fill handle.

Using Fill Handle to Copy Same Formula

  • Drag the fill handle below and you will get random values in cells B5:B19.
Note:

As these are randomly generated values, the values will change randomly every time you do any operation with these cells. So, to make the random values constant, you need to copy and paste values.

  • To make the random values constant, select all the cells (B5:B19 here) >> press Ctrl + C key to copy the cells with the formula.

Copying Random Values with Formula

  • Right-click again and you will see an option Value from the Paste Options: of the context menu.

Pasting Only Values

  • Click on the Value option and you will be able to convert all those ever-changing random values to constant random values. These values won’t change anymore if you do any operation with these values.

Random Values

  • To adjust the random values in order to find the total of 50000, click on cell C5 and insert the following formula.
=B5/SUM($B$5:$B$19)*$E$9

Formula to Adjust Random values

  • Press Enter and the random value of cell B5 will be adjusted to a value based on all random values present.
  • Place your cursor in the bottom right position of the cell and you find the fill handle.

Using Fill Handle to Copy Same Formula

  • Drag the fill handle downward and you will find all the adjusted values of the present random values. If you sum these values, you will get your desired total of 50000.

Adjusted Random Values to Get Certain Result


Download Practice Workbook

You can download our practice workbook from here for free!


So, in this article, we have shown you all the suitable ways to sum random cells in Excel. Based on your requirements, you can follow any of these methods easily. We have also shown you how to sum random cells of different worksheets in Excel. Besides, we have shown you how to generate random values in Excel and then adjust them in order to sum those cells and get a predetermined result.

Hope, this article helps you a great deal. If you have any further queries or recommendations, please feel free to comment here. Thank you!


Related Articles


<< Go Back to Sum in Excel | Calculate in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Chinmoy Mondol
Chinmoy Mondol

Chinmoy Mondol is a computer engineer who loves exploring Excel and VBA programming. Programming, to him, is a time-saving tool for efficiently managing data, files, and internet tasks. He is proficient in C, C++, Python, JavaScript, HTML, SQL, MySQL, PostgreSQL, Microsoft Office, and VBA and goes beyond the basics. He holds a B.Sc. in Computer Science and Engineering from American International University-Bangladesh. He has transitioned into the role of a content developer. Beyond his work, find him immersed... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo