[FAQ000158]Is there a way to dynamically specify the selection range for the SUM function?

Q

I want to use the SUM function to get the total of a selection of cells,

but each time the selection of cells to be totaled changes.

Is there any way to specify the cell selection dynamically?

A

If you want to dynamically specify the cell selection for the SUM function, you can use the following technique.

Both will give the same result.

(1) How to write directly in a cell

Calculate the selected range of cells, which is the argument of the SUM function, using string concatenation.

The string of the selected range of the merged cells can be used as an argument of the SUM function

by indirectly referencing it with the INDIRECT function.

e.g.) =SUM(INDIRECT("AB11:AB" & L1))

The value to be varied is set in cell L1.

For example, if the value of cell L1 in the above formula is "20",

the result of the formula would be "=SUM(INDIRECT("AB11:AB20"))".

(2) How to use action

Use the "Set [ ] to cell [ ]" action in the "Cells" tab.

Combine the whole, including the SUM function, as a string and set it in a cell.

e.g.) ="=SUM(AB11:AB" & L1 & ")"

The value to be varied is set in cell L1.

For example, if the value of cell L1 in the above formula is "20",

The result of the formula will be "=SUM(AB11:AB20)".

Related keywords

Formula