Sumproduct: Year To Date

excel sum by year sumif by month and year sumifs month and yearHow do you write dynamic YTD formulas in Excel?

I’ll show you how using SUMPRODUCT, OFFSET & SUM functions.

In this video, you’ll see 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.

Calculating, YTD price can be a bit trickier.

To get the volume weighted average price, you need to multiply the volume by the price and add each month to the next up to the current month.

Then divide that value by the YTD volume.

This is when knowing how to use Excel’s advanced formulas together can become helpful.

Here the SUMPRODUCT function together with OFFSET can come to the rescue.

Watch the steps in this video:

The new Excel Dashboards course is here!

Now available on Udemy

Visually Effective Excel Dashboards for your reports.
Redeem your coupon for 75% off below.

GET 75% OFF THE COURSE
Free Ebook

4 Comments

  1. Glenn December 2, 2017 at 7:05 am - Reply

    hi Is there a way to dynamically calculate YTD as per above but instead of using dates based on months I can use dates based on weeks?
    I have a table which contains week dates in one column and values in another column.

    Thank you

    • Leila Gharani December 4, 2017 at 7:36 pm - Reply

      Yes – you can use it the same way if you have weeks that you need added instead of months. =weeknum() formula might help if you need to find out when you cross over to the next year….

  2. Akmaral May 28, 2018 at 7:02 pm - Reply

    Hi Leila, thank you so much for your sharings! I did everything as in video step by step, unfortunately for the period from Jun thru Sep I got wrong figures. I reapplied the firmula checked each step, formats. No way ((( I work on MS Excel 2010. How do you you think what’s wrong? Thank you in advance!

    • Leila Gharani June 4, 2018 at 10:06 am - Reply

      Hi – the technique works on older Excel versions as well. Make sure the months are input as dates. I’m not sure if you’re in Advanced Excel course – if yes, cross-check the formatting with the file inside the course. This example is included there.

Leave A Comment

Share This