This video demonstrates how to sum between two dates in Excel. In our scenario we want to calculate total revenue. The functions used in this video are SUMIFS, EOMONTH and EDATE.
Download the featured file here.
The formula used is:
=SUMIFS(Revenue_data[Revenue],Revenue_data[Date],”>=”&I2,Revenue_data[Date],”<=”&J2)
One thing to note with this formula is how you create criteria that combines a comparison operator with a cell reference, for example “>=”&I2. The comparison operator is placed within double inverted commas and an ampersand is used to join or concatenate the comparison operator with the cell reference.
The formula uses structured references when referring to the revenue data, this is because the revenue data is held in a table. The reference to columns in the data include the table name Revenue_data and the column name [Date]. There are a number of benefits afforded to you when you place your data in a table; in this context it allows us two. Firstly, we can refer to columns without manually selecting them and secondly a table creates a dynamic range, so if we add more data the formula will automatically update.