Formula to add up data for 1 year automatically

[Bobbbo]

I always have trouble dealing with dates in Excel. For those that don’t this should be easy. The attached spread sheet has columns of data with each column represented by a date in row 4. I want to total the data for the whole year knowing that the last dated column will not be the end of the current year. In this case the year is 2016. I want a formula that looks at the last column, cell BB4, and determine the year, then have a formula that can calculate going back 3 years to add up the data for 2016 (from cell BB4) minus 3 years which equals 2013 then add up the data from 1/31/2013 through 12/31/2013 in the 2013 column, cell BD5. The formula will go in cell BD5. Then do the same for minus 2 years (2014), and 1 year (2015) for each item 1 through 3. I also what the cell BD4, BE4 and BF4 to have a formula that calculates the date based on the year in BB4. Then do the same for the other rows 6 and 7. I have the formula that I created manually in column BD, BE and BF just to illustrate what is needed.

[JohnTopley]

In BD5

=SUMPRODUCT(($F5:$BB5)*(YEAR($F$4:$BB$4)=BD$4))

copy across and down

OR

=SUMIFS($F5:$BB5,$F$4:$BB$4,">=01/01/" &BD$4,$F$4:$BB$4,"<=31/12/" &BD$4)

[Bobbbo]

It works great using the first formula. Thank you so much. What formula do I use to populate the year in cells BD4, BE4 and BF4?

[JohnTopley]

try

in BF4

=IF(MONTH(LOOKUP(9999999,F$4:BB$4))=12,YEAR(TODAY()),YEAR(TODAY())-1)

This checks if month of the last date i.e in BB4 is 12: if it is, set year=current year otherwise set to current year -1

in BE4

=BF4-1

in BD4

=BE4-1 (Or BF4-2)

[Bobbbo]

Hi John, While waiting for your response I came up with this that is simpler. for cell BD4 =(YEAR($BB$4))-3, then BE4 =(YEAR($BB$4))-2 and then BF4 =(YEAR($BB$4))-1.

Do you see any issue with this formula?

[JohnTopley]

What happens when BB4 is 31/12/2016? Do you want years 2016, 2015, 2014 or 2015, 2014, 2013: your solution would give the latter. If you are OK with this, then use your formula.

[Bobbbo]

Do you mean 12/31/2016, if so I put that in cell BB4 and still get 2013, 2014 and in cells 2015 BD4 through BF4. Am I missing something?

[JohnTopley]

If you decide to go my way then this is correct:

=IF(MONTH(LOOKUP(9999999,F$4:BB$4))=12,YEAR(BB4)),YEAR(BB4)-1)

[JohnTopley]

what I am saying if you have full year's data ending with 31/12/yyyy in BB4 then (in my view) YYYY should be date in BF4. See my previous post.

You know what you want!!!

[Bobbbo]

Now I see what you mean. I tried your formula in BD4 and I get an error.

[JohnTopley]

See attached: formula in BH4

[Bobbbo]

Great I have it working. There were few parenthesis that were in the wrong place. Again thanks for all your help!

[JohnTopley]

You are very welcome,

[Bobbbo]

Hi John, Since you knew the answer to my last question so easily, can you adapt your formula so that instead of adding up the whole year, it just returns the value in the column for the last day of that year i.e. 12/31/2103?

[JohnTopley]

Try

=INDEX($F5:$BD5,MATCH(DATE(BD$4,12,31),$F$4:$BB$4,0))

[Bobbbo]

WOW you are great, just what I needed. Thank You, Thank You Thank You

[Bobbbo]

John, WOW that works great. Thank You, Thank You, Thank You!!