Dynamic SUM in Excel

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.

Lets's see how it works with this basic example.

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:

=SUM(Table1[Orders])

Now if we add new values to our data, we don't have to change the formula because the named range of the table has been automatically increased.

 

 

 

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

On its standard use, INDEX returns a value inside a table like the VLOOKUP function. But there is also another use of INDEX.

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)

First we need to find the position of the selected date in the range of dates. That's exactly the purpose of the MATCH function.

 

=MATCH(H4,E2:E13,0)

 

 

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)

In our example, the range is the range of value (in column F) and the position is the position of the date returned by the MATCH function.

 

=INDEX(F2:F13,MATCH(H4,E2:E13,0))

 

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.

=SUM(F2:INDEX(F2:F13,MATCH(H4,E2:E13,0)))

Related posts


Permanent link to this article: https://www.excel-exercise.com/dynamic-sum-in-excel/


Leave a Reply

Your email address will not be published.