Create a SUM is something very easy with Excel. But when you want to add only some specific cells and not all of them, the formula is not so easy but it's possible. Let's see how to create a dynamic SUM.
Insert your data in a Table
But before to explain how to create the formula, maybe you can solve your problem with a table.
Insert your data in a table is very useful because your formulas refer to a named range and not the references of cells.
We have a list of orders for each customer.
We create the formula SUM to calculate the sum of the orders. But here, instead of using the references of the cells, we use the name of the columns.
The formula is:
Dynamic SUM - Presentation of the context
In some circonstancies, you can't use a Table to create a dynamic SUM. It's the case when you have for instance payments received and payments expected.
In the following document, we have a document with the estimate payment to refund an investment. But each month we readjust the document with the real payment.
So we must build a dynamic range to indicate that the sum will be calculated till a specific date
Integration of the INDEX function in the formula
INDEX returns the reference of a cell an not the value when the function is use inside a range of cells.
The difficulty here is to trust your writing because you can't visualise the result in a cell.
Meaning of the MATCH function
The dynamic sum is linked to a date in the data validation (cell H4 in our example)
Meaning of the INDEX function
The INDEX function returns a value inside a range a data. The second parameter of INDEX is the position of the element we want to return.
The INDEX function as the following writing
=INDEX(range,position row, position column)
INDEX returns a reference
But the INDEX fonction also returns the reference of the result.
So if you integrate the previous formula inside the range of a SUM function, the INDEX will return the reference of the cell and not the contain of the cell.