Excel Trick Lets You Sum a Number of Cells With a Variable Range

Configurare noua (How To)

Situatie

In Excel, you can sum a number of cells using a variable range with the INDIRECT function. The INDIRECT function automatically updates the range of cells you’ve referenced without manually editing the formula itself. You can use the INDIRECT function with any number of Excel functions, but the most common (and useful) is when you use the SUM function.

Solutie

You can embed the INDIRECT function as an argument inside of the SUM function to create a variable range of cell references for the SUM function to add. The INDIRECT function does this by referencing the range of cells indirectly, through an intermediate cell reference.

The format of both functions used together looks like this:

=SUM(INDIRECT

This locks the range to start at D1 and allows D4 to change if you insert or delete any cells in the D column.

You can also use other cells in the spreadsheet to modify the cell references. For example, if you use E1 to reference the first cell of the range and E2 to reference the last cell of the range, the formula looks like this:

=SUM(INDIRECT(“D” &E1& “

Try the SUM and INDIRECT Functions

By changing the numbers located in cells E1 and E2, you can modify the range in the formula without having to manually edit the formula.

Create a spreadsheet to test the SUM and INDIRECT functions yourself. Begin by creating a blank spreadsheet and entering the following data into columns D and E:

Cell Data

D1 – 5

D2 – 10

D3 – 15

D4 – 20

D5 – 25

D6 – 30

E1 – 1

E2 – 4

  • Next, create the formula in cell F1. Here’s how:
  • Select cell F1. This is where the result of this example will display.
  • Select Formulas.
  • Choose Math & Trig to open the function drop-down list.
  • Select SUM in the list to open the SUM Function Arguments dialog box

Tip solutie

Permanent

Voteaza

(6 din 15 persoane apreciaza acest articol)

Despre Autor

Leave A Comment?