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**.

*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.

**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!

**Related Articles**

- How to Sum in Excel If the Cell Color Is Red
- How to Sum Colored Cells in Excel Without VBA
- [Solved!] Currency Sum Not Working in Excel
- How to Sum Visible Cells with Criteria in Excel
- How to Ignore Blank Cells in Excel Sum

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