That's exactly the purpose of the MATCH function. The dynamic sum is linked to a date in the data validation cell (cell H4 in our example)įirst we need to find the position of the selected date in the range of dates. The difficulty here is to trust your writing because you can't visualise the result in a cell. INDEX returns the reference of a cell, and not the value, when the function is used inside a range of cells. In its standard use, INDEX returns a value inside a table like the VLOOKUP function. Integration of the INDEX function in the formula So we build a dynamic range to indicate that the sum will be calculated up to a specific date. But each month we readjust the document with the actual amount paid.

In the example below, we use Excel VLOOKUP and SUM to calculate the total sales of Barberton Daisy for 6 months, from January to June. 1: Excel VLOOKUP and SUM multiple columns. In the following Excel file, we have the estimated monthly amount due to be repaid on an investment. Let’s explore some examples of using VLOOKUP and SUM functions together in one formula in Excel. This is the case when you have, for instance, payments received and payments due.

In some circumstances, you can't use a Table to create a dynamic SUM.

Make a sum excel for mac