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.
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.
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
- 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.
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)
- Press Enter and you will get your desired result.
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.
- 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)
- Press Enter. You will get your desired sum result of random cells.
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.
- 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)
- Press Enter and you will get your desired result which is $31409.15.
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.
- 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.
- 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.
- Click on the OK button and you will get your desired sum result.
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,"")
- 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.
- 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.
- 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.
- 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.
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)
- Press Enter and you will get the total sales of Abigail.
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.
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)
- 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.
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()
- 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.
- Drag the fill handle below and you will get random values in cells B5:B19.
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.
- Right-click again and you will see an option Value from the Paste Options: of the context menu.
- 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.
- 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
- 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.
- 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.
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!
