Year-to-Date Sums and Averages with different start dates

Hello,

I have many phone book ads for which I am tracking the number of unique calls each month. While I have data dating back to 2014, I would like to keep a YTD total and YTD average number of calls. The problem is that the books are all published in different months—so, if one book is published in March, then its "year" would start in March, vs. a book that is published in July, and so on. In the example attached I highlighted each range I would like to calculate. I know that I could manually change the formula for each month, but there are many more books than I show in the example—I was hoping to find a formula based on an entry in another cell, which is why I included columns D & E (and made C number-formatted). Also, if it's worth noting, I will continue to be adding data to this spreadsheet (i.e., into 2016).

Any advice would be appreciated, thanks!


RG_Example.xlsx

It can be done, but your existing format is not completely formula friendly.

Starting with F2, change 1 to 1/1/2014
Then in G2, change 2 to 2/1/2014 (I assume that you use the standard US date format, month/day/year)
Select both of these cells and use the fill handle (move your mouse to the bottom right of G2 so that the + cursor changes from white to black), then hold left click and drag to AC2. The cells will all show #### you can ignore that for a second.

Next change the number format of those cells to a custom format of m This will give you real dates in those cells, but they will still appear as month only, so it will look the same as before.

Now, assuming that column C contains the date of the most recent publication, this formula entered in AD3 and filled down will give you total year to date.

=SUMIFS(F3:AC3,$F$2:$AC$2,">"&EOMONTH(C3,-1),$F$2:$AC$2,"<="&EOMONTH(C3,12))

And this in in AE3 will give you the average.

=IFERROR(AVERAGEIFS(F3:AC3,$F$2:$AC$2,">"&EOMONTH(C3,-1),$F$2:$AC$2,"<="&EOMONTH(C3,12)),0)

These both work. Thank you for your help!!