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,516
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

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

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,516
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,119,102
Messages
5,576,135
Members
412,699
Latest member
Dmetcalf2021
Top