How to Sum Random Cells in Excel

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.