Dynamic SUM in Excel

Dynamic SUM in Excel
Last Updated on 28/02/2021
Reading time: 3 minutes

Creating a SUM is very easy to do in Excel. But when you want to add only specific cells and not all of them, the formula is not as easy. But it's possible! Let's see how to create a dynamic SUM.

Example dynamic SUM Excel

Insert your data into a Table, the easiest solution

If your need is to dynamically update your sum when you add new data, the easiest solution is to insert your data in a Table. Inserting your data into a table is very useful because your formulas refer to a named range and not to the references of cells.

Let'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 automatically
been increased.

Dynamic SUM with the INDEX function

In some circumstances, you can't use a Table to create a dynamic SUM. This is the case when you have, for instance, payments received and payments due.

In the following Excel file, we have the estimated monthly amount due to be repaid on an investment. But each month we readjust the document with the actual amount paid.

So we build a dynamic range to indicate that the sum will be calculated up to a specific date.

Integration of the INDEX function in the formula

In 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, and not the value, when the function is used 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 (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 data range. The second parameter of INDEX is the position of the element we want to return.

The INDEX function is written as follows:

=INDEX(range,position row, position column)

In our example the range is the range of a 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 contents of the cell.

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

1 Comment

  1. Sanjiv Kumar Shrivastava
    12/10/2022 @ 20:08

    Excellent explanation.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Dynamic SUM in Excel

Reading time: 3 minutes
Last Updated on 28/02/2021

Creating a SUM is very easy to do in Excel. But when you want to add only specific cells and not all of them, the formula is not as easy. But it's possible! Let's see how to create a dynamic SUM.

Example dynamic SUM Excel

Insert your data into a Table, the easiest solution

If your need is to dynamically update your sum when you add new data, the easiest solution is to insert your data in a Table. Inserting your data into a table is very useful because your formulas refer to a named range and not to the references of cells.

Let'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 automatically
been increased.

Dynamic SUM with the INDEX function

In some circumstances, you can't use a Table to create a dynamic SUM. This is the case when you have, for instance, payments received and payments due.

In the following Excel file, we have the estimated monthly amount due to be repaid on an investment. But each month we readjust the document with the actual amount paid.

So we build a dynamic range to indicate that the sum will be calculated up to a specific date.

Integration of the INDEX function in the formula

In 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, and not the value, when the function is used 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 (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 data range. The second parameter of INDEX is the position of the element we want to return.

The INDEX function is written as follows:

=INDEX(range,position row, position column)

In our example the range is the range of a 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 contents of the cell.

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

1 Comment

  1. Sanjiv Kumar Shrivastava
    12/10/2022 @ 20:08

    Excellent explanation.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *