summarizing different series with different starting points (tricky)

[karolinem]

Hi,

I have different value series (light blue cells) whose first value is attached to a different "starting month".
For example, in February 2020, one value series is starting with the value 9 in February 2020, the value 51 in March 2020, the value 83 in April 2020 etc.
I have another value series starting in August 2020, with the value 8 in August 2020, the value 48 in September 2020, the value 79 in October 2020 etc.

I want these different value series to be summarized and attributed to the right month (illustrated in the bottom)
So, for January 2020 - July 2020 I want the values 9, 51, 83, 90, 274, 339 but for August 2020 I want 600 + 8 (7th value of the first series and 1st value of the second series). For September 2020, I want 699 + 48 + 2 (8th value of the first series, 2nd value of the second series and 1st value of the third series).

And so on.

Does anyone know how to get a dynamic formula to summarize the values like above?

 

[KRice]

Do the rows always represent sequential months?...with no months skipped?

 

[KRice]

Assuming the answers to my questions are that rows always represent sequential months with none skipped, here is one approach:

MrExcel20200915.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	date	city	reachable citizens	total citizens	citizens share
2	Jan-20
3	Feb-20					9	51	83	90	274	339	600	699	848	947	1009	1260	1362	1430
4	Mar-20
5	Apr-20
6	May-20
7	Jun-20
8	Jul-20
9	Aug-20					8	48	79	85	260	322	570	664	805	899	957	1196	1293	1357
10	Sep-20					2	11	18	20	60	75	132	154	187	208	222	277	300	314
11	Oct-20					4	26	42	46	139	172	304	354	430	480	511	638	690	724
12	Nov-20					2	13	22	24	72	89	157	183	223	248	265	330	357	375
13	Dec-20
14	Jan-21
15	Feb-21
16	Mar-21					5	31	50	55	167	206	365	425	516	576	613	766	829	870
17
18
19						Jan-20	Feb-20	Mar-20	Apr-20	May-20	Jun-20	Jul-20	Aug-20	Sep-20	Oct-20	Nov-20	Dec-20	Jan-21	Feb-21
20						0	9	51	83	90	274	339	608	749	942	1078	1344	1710	2170
karolinem

Cell Formulas
Range	Formula
F20:S20	F20	=SUM((COLUMN(OFFSET($F$2,,,COLUMNS($F19:F19),COLUMNS($F19:F19)))+ROW(OFFSET($F$2,,,COLUMNS($F19:F19),COLUMNS($F19:F19)))=COLUMN($F$2)+ROW($F$2)+COLUMNS($F19:F19)-1)*OFFSET($F$2,,,COLUMNS($F19:F19),COLUMNS($F19:F19)))
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.