We want to calculate the Total Sales of the Sales Representative **Abigail** from the Sales Report shown below.

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

## Method 1 – Using Simple Arithmetic Calculations to Sum Random Cells in Excel

We have a dataset of January sales containing **Date**, **Sales Rep,** and **Sales **columns. We have sales amounts for sales representatives with their respective dates of sale. We want to sum up the total **January **sales of **Abigail**.

**Steps:**

- Select
**cell F9**and write an**equal (=)**sign, then click on the cells that contain the sales amount of Abigail and separate each of those values by a**plus (+)**sign. - The formula will look like:

`=D5+D9+D10+D14+D16`

- Press
**Enter**.

## Method 2 – Applying the SUM Function to Sum Non-Contiguous Cells in Excel

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

## Method 3 – Using the AutoSum Feature

**Steps:**

- Select the cell where you want your sum result (
**cell F9**here). - Go to the
**Home**tab and the**Editing**group, then select the**AutoSum**option.

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

## Method 4 – Applying Named Ranges to Sum Random Cells in Excel

**Steps:**

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

## Method 5 – Using the Insert Function Button to Sum Random Cells in Excel

**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**dialog box. - Select
**SUM**from the options available in the**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.

## Method 6 – Using the Subtotal Tool to Sum Non-Contiguous Cells in Excel

**Steps:**

- Create a new column named
**Abigail Sales**and insert the following formula in**cell E5**.

`=IF(C5="Abigail",D5,"")`

- Press
**Enter.** - Place your cursor in the
**bottom-right**corner of**cell E5**and you will find Excel’s**fill handle**.

- Drag the
**fill handle down**and you will get all the sales amount of Abigail in column**E**. - Select cells
**D4:E19.** - Go to the
**Data**tab and select the**Subtotal**tool.

- You will get a
**Subtotal**dialog box. - Choose
**Abigail Sales**in the**At each change in:**dropdown. - Choose
**Sum**in the**Use function:**dropdown. - Select the
**Abigail Sales**checkbox in the**Add Subtotal to:**options.

- Click on the
**OK**button.

## Method 7 – Applying the SUMIF Function to Sum Random Cells Based on Criteria in Excel

**Steps:**

- To find the total sales of Abigail in
**cell F9**, insert the following formula.

`=SUMIF(C5:C19,"Abigail",D5:D19)`

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

## Method 8 – Summing Random Cells Across Multiple Sheets in Excel

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

## Method 9 – Summing Random Cells to Get a Certain Value in Excel

We have a specific target value and we want to achieve that target value with randomly generated values.

**Steps:**

- Insert your target value (
**50,000**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()`

- Hover over the fill handle for B5.

- Drag the
**fill handle down**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.*

- Select all the cells (
**B5:B19**here) and press**Ctrl + C**key to copy the cells with the formula.

**Right-click**on the selection and select**Values**from the**Paste Options:**of the context menu.

- This converts the values to constants.

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

- Drag the
**fill handle down**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 the Practice Workbook**

**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 Ignore Blank Cells in Excel Sum

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