Sum cells dynamically based on Date

anonemous

New Member
Joined
Dec 2, 2015
Messages
10
I have a worksheet where each month, a new column is inserted to the front with the past month's worth of data.
There are two separate columns to output Year to Date sums along with the change in percentage, which would be Jan-March 2020 compared with Jan-March 2019 and the change. (highlighted in red below)

B2 = SUM(E2:G2)
C2 = SUM(Q2:S2)
D2 = (c2-b2)/b2

How can I modify these columns so that they will do it automatically each month instead of having to revise my formula each month?
I've found that sometimes I might make mistakes and not realize the YTD is summing the wrong month.
Previously I've found that I can use INDIRECT to keep cells static, but this doesn't work when each month is dynamic in a YTD format.

mrexcel_04082020.jpg


Advice always appreciated from here!
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,479
Office Version
  1. 365
Platform
  1. Windows
I've updated my environment
Thanks for that.

Try these, copied down.

anderma8 2020-04-22 1.xlsm
ABCDEFGHIJKLMNOPQRSTUVWXYZ
1LocationJan-19Feb-19Mar-19Apr-19May-19Jun-19Jul-19Aug-19Sep-19Oct-19Nov-19Dec-192019 TotalJan-20Feb-20Mar-20Apr-20May-20Jun-20Jul-20Aug-20Sep-20Oct-20Nov-20Dec-20
2Location 146,04943,74844,34842,88039,92036,56735,54542,48335,70938,27031,95628,725466,20042,07032,56331,683
3Location 211,64710,51112,65913,59912,52160,937
4Location 356,00555,03767,21048,63065,95251,20355,90247,71049,09350,49652,53725,190624,96551,55649,79655,623
5Location 455,59650,59757,13152,71053,23759,27957,23951,85650,20358,44454,47813,473614,24312,36354,53952,832
6Location 513,01413,18711,84613,48114,76613,50613,62613,42013,37413,87913,41011,232158,74113,07711,03613,998
7Location 637,08235,82039,17828,41634,40225,36336,66240,07337,37943,01535,49126,095418,97635,76429,54933,102
8
9
101/03/2020Current YTDPrior Year
11Location 1106,316134,145
12Location 2034,817
13Location 3156,975178,252
14Location 4119,734163,324
15Location 538,11138,047
16Location 698,415112,080
YTD (4)
Cell Formulas
RangeFormula
B11:B16B11=SUMPRODUCT(O$2:Z$7,(MONTH(O$1:Z$1)<=MONTH(A$10))*(A$2:A$7=A11))
C11:C16C11=SUMPRODUCT(B$2:M$7,(MONTH(B$1:M$1)<=MONTH(A$10))*(A$2:A$7=A11))
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

anderma8

New Member
Joined
Oct 29, 2008
Messages
36
Office Version
  1. 365
Platform
  1. Windows
I have to say - THANK YOU!!!!!! This is going to make my life SOOOO much easier! I never worked with Sumproduct before... I'll definitely be using this!
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,479
Office Version
  1. 365
Platform
  1. Windows
I have to say - THANK YOU!!!!!! This is going to make my life SOOOO much easier! I never worked with Sumproduct before... I'll definitely be using this!
You're welcome. (y)
 

Watch MrExcel Video

Forum statistics

Threads
1,118,186
Messages
5,570,747
Members
412,339
Latest member
sstackho
Top