Excel Dynamic YTD Calculations: OFFSET, SUMPRODUCT and SUM

Excel Dynamic YTD Calculations: OFFSET, SUMPRODUCT and SUM

 

 

This video shows you how to write dynamic YTD formulas in Excel using SUMPRODUCT, OFFSET & SUM functions. In this video, I'll show you a way of writing YTD formulas (in this specific case for volume and price) when your data set goes over a few years. i.e. each month occurs more than once.

 

If you were doing this manually, you'll need to revise your formula in the middle of your data set to make your range start from January of the next year. OFFSET allows you to create dynamic ranges - when you use this with the Month() and SUM() functions, you've created a smart formula that recognizes when the data goes over to the next year and revises your YTD calculation.

Related topics:

• Excel VBA Arrays: Practical Example of a 2 dimensional array to create a New Workbook
• How to use the Message Box in Excel VBA (syntax you'll need for msgbox) 
• Excel VBA: Loop through cells inside the used range (For Each Collection Loop)
• Excel VBA IF THEN Statement (with ELSEIF and Looping in cells)