Suming values from two columns

mkuzner

New Member
Joined
Mar 14, 2014
Messages
11
Hi everybody!

I'm trying to solve the assignment I got in my Informatics class and would really appreciate some help.


In the first table there is data, which contains numbers for monthly (M) and yearly (Y) inflation for each month and year. My goal is, in the second table, to get the SUM of last 12 monthly inflations depending on the chosen date. For example, in the copied case, I would need to sum the data from April 2007 to May 2006.
By the way, in table 2 date (month/year) is inserted as a value (4/1/2007) and not as text.

I've tried everything I know, but simply cannot get the right idea. Therefore, HELP. J Thank you!


[TABLE="width: 432"]
<tbody>[TR]
[TD="width: 64, bgcolor: transparent"]Table 1:[/TD]
[TD="width: 64, bgcolor: transparent"]M[/TD]
[TD="width: 64, bgcolor: transparent"]Y[/TD]
[TD="width: 64, bgcolor: transparent"]M[/TD]
[TD="width: 64, bgcolor: transparent"]Y[/TD]
[TD="width: 64, bgcolor: transparent"]M[/TD]
[TD="width: 64, bgcolor: transparent"]Y[/TD]
[TD="width: 64, bgcolor: transparent"]M[/TD]
[TD="width: 64, bgcolor: transparent"]Y[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]Year /
Month
[/TD]
[TD="width: 128, bgcolor: transparent, colspan: 2"]2008[/TD]
[TD="width: 128, bgcolor: transparent, colspan: 2"]2007[/TD]
[TD="width: 128, bgcolor: transparent, colspan: 2"]2006[/TD]
[TD="width: 128, bgcolor: transparent, colspan: 2"]2005[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]dec[/TD]
[TD="bgcolor: transparent"]-0,60[/TD]
[TD="bgcolor: transparent"]2,10[/TD]
[TD="bgcolor: transparent"]0,40[/TD]
[TD="bgcolor: transparent"]5,60[/TD]
[TD="bgcolor: transparent"]0,40[/TD]
[TD="bgcolor: transparent"]2,80[/TD]
[TD="bgcolor: transparent"]0,00[/TD]
[TD="bgcolor: transparent"]2,30[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]nov[/TD]
[TD="bgcolor: transparent"]-0,70[/TD]
[TD="bgcolor: transparent"]3,10[/TD]
[TD="bgcolor: transparent"]0,90[/TD]
[TD="bgcolor: transparent"]5,70[/TD]
[TD="bgcolor: transparent"]0,30[/TD]
[TD="bgcolor: transparent"]2,30[/TD]
[TD="bgcolor: transparent"]-0,50[/TD]
[TD="bgcolor: transparent"]2,10[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]oct[/TD]
[TD="bgcolor: transparent"]0,00[/TD]
[TD="bgcolor: transparent"]4,90[/TD]
[TD="bgcolor: transparent"]0,70[/TD]
[TD="bgcolor: transparent"]5,10[/TD]
[TD="bgcolor: transparent"]-0,80[/TD]
[TD="bgcolor: transparent"]1,50[/TD]
[TD="bgcolor: transparent"]0,20[/TD]
[TD="bgcolor: transparent"]3,10[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]sep[/TD]
[TD="bgcolor: transparent"]0,00[/TD]
[TD="bgcolor: transparent"]5,50[/TD]
[TD="bgcolor: transparent"]0,40[/TD]
[TD="bgcolor: transparent"]3,50[/TD]
[TD="bgcolor: transparent"]0,40[/TD]
[TD="bgcolor: transparent"]2,50[/TD]
[TD="bgcolor: transparent"]1,00[/TD]
[TD="bgcolor: transparent"]3,20[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]aug[/TD]
[TD="bgcolor: transparent"]-0,60[/TD]
[TD="bgcolor: transparent"]6,00[/TD]
[TD="bgcolor: transparent"]0,30[/TD]
[TD="bgcolor: transparent"]3,50[/TD]
[TD="bgcolor: transparent"]0,60[/TD]
[TD="bgcolor: transparent"]3,20[/TD]
[TD="bgcolor: transparent"]-0,60[/TD]
[TD="bgcolor: transparent"]2,10[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]jul[/TD]
[TD="bgcolor: transparent"]0,00[/TD]
[TD="bgcolor: transparent"]6,90[/TD]
[TD="bgcolor: transparent"]0,00[/TD]
[TD="bgcolor: transparent"]3,80[/TD]
[TD="bgcolor: transparent"]-0,20[/TD]
[TD="bgcolor: transparent"]1,90[/TD]
[TD="bgcolor: transparent"]0,70[/TD]
[TD="bgcolor: transparent"]2,30[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]jun[/TD]
[TD="bgcolor: transparent"]0,90[/TD]
[TD="bgcolor: transparent"]7,00[/TD]
[TD="bgcolor: transparent"]0,40[/TD]
[TD="bgcolor: transparent"]3,60[/TD]
[TD="bgcolor: transparent"]-0,30[/TD]
[TD="bgcolor: transparent"]2,90[/TD]
[TD="bgcolor: transparent"]0,10[/TD]
[TD="bgcolor: transparent"]1,90[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]may[/TD]
[TD="bgcolor: transparent"]1,10[/TD]
[TD="bgcolor: transparent"]6,40[/TD]
[TD="bgcolor: transparent"]1,20[/TD]
[TD="bgcolor: transparent"]2,90[/TD]
[TD="bgcolor: transparent"]0,90[/TD]
[TD="bgcolor: transparent"]3,20[/TD]
[TD="bgcolor: transparent"]0,30[/TD]
[TD="bgcolor: transparent"]2,20[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]apr[/TD]
[TD="bgcolor: transparent"]0,80[/TD]
[TD="bgcolor: transparent"]6,50[/TD]
[TD="bgcolor: transparent"]1,10[/TD]
[TD="bgcolor: transparent"]2,60[/TD]
[TD="bgcolor: transparent"]0,80[/TD]
[TD="bgcolor: transparent"]2,70[/TD]
[TD="bgcolor: transparent"]0,00[/TD]
[TD="bgcolor: transparent"]2,70[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]mar[/TD]
[TD="bgcolor: transparent"]1,30[/TD]
[TD="bgcolor: transparent"]6,90[/TD]
[TD="bgcolor: transparent"]1,00[/TD]
[TD="bgcolor: transparent"]2,30[/TD]
[TD="bgcolor: transparent"]0,80[/TD]
[TD="bgcolor: transparent"]1,90[/TD]
[TD="bgcolor: transparent"]1,10[/TD]
[TD="bgcolor: transparent"]3,10[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]feb[/TD]
[TD="bgcolor: transparent"]0,00[/TD]
[TD="bgcolor: transparent"]6,50[/TD]
[TD="bgcolor: transparent"]-0,20[/TD]
[TD="bgcolor: transparent"]2,10[/TD]
[TD="bgcolor: transparent"]0,40[/TD]
[TD="bgcolor: transparent"]2,20[/TD]
[TD="bgcolor: transparent"]0,60[/TD]
[TD="bgcolor: transparent"]2,60[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]jan[/TD]
[TD="bgcolor: transparent"]0,10[/TD]
[TD="bgcolor: transparent"]6,40[/TD]
[TD="bgcolor: transparent"]-0,70[/TD]
[TD="bgcolor: transparent"]2,70[/TD]
[TD="bgcolor: transparent"]-0,50[/TD]
[TD="bgcolor: transparent"]2,40[/TD]
[TD="bgcolor: transparent"]-0,60[/TD]
[TD="bgcolor: transparent"]2,20[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 240"]
<tbody>[TR]
[TD="width: 64, bgcolor: transparent"]Table 2:[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, colspan: 2"]Month/Year[/TD]
[TD="width: 192, bgcolor: transparent, colspan: 3"]Sum of monthly inflations for the last 12 months[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, colspan: 2"]apr.07[/TD]
[TD="bgcolor: transparent, colspan: 3"]RESULT[/TD]
[/TR]
</tbody>[/TABLE]
 
[TABLE="width: 1037"]
<colgroup><col width="64" style="width: 48pt;" span="9"> <col width="742" style="width: 557pt; mso-width-source: userset; mso-width-alt: 27136;"> <col width="64" style="width: 48pt;"> <tbody>[TR]
[TD="width: 64, bgcolor: transparent"]Table 1:[/TD]
[TD="width: 64, bgcolor: transparent"]M[/TD]
[TD="width: 64, bgcolor: transparent"]Y[/TD]
[TD="width: 64, bgcolor: transparent"]M[/TD]
[TD="width: 64, bgcolor: transparent"]Y[/TD]
[TD="width: 64, bgcolor: transparent"]M[/TD]
[TD="width: 64, bgcolor: transparent"]Y[/TD]
[TD="width: 64, bgcolor: transparent"]M[/TD]
[TD="width: 64, bgcolor: transparent"]Y[/TD]
[TD="width: 742, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]Year /[/TD]
[TD="width: 64, bgcolor: transparent"]2008[/TD]
[TD="width: 64, bgcolor: white"] [/TD]
[TD="width: 64, bgcolor: transparent"]2007[/TD]
[TD="width: 64, bgcolor: white"] [/TD]
[TD="width: 64, bgcolor: transparent"]2006[/TD]
[TD="width: 64, bgcolor: white"] [/TD]
[TD="width: 64, bgcolor: transparent"]2005[/TD]
[TD="width: 64, bgcolor: white"] [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: white"]Month[/TD]
[TD="width: 64, bgcolor: white"] [/TD]
[TD="width: 64, bgcolor: white"] [/TD]
[TD="width: 64, bgcolor: white"] [/TD]
[TD="width: 64, bgcolor: white"] [/TD]
[TD="width: 64, bgcolor: white"] [/TD]
[TD="width: 64, bgcolor: white"] [/TD]
[TD="width: 64, bgcolor: white"] [/TD]
[TD="width: 64, bgcolor: white"] [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]dec[/TD]
[TD="width: 64, bgcolor: transparent"]-0.6[/TD]
[TD="width: 64, bgcolor: transparent"]2.1[/TD]
[TD="width: 64, bgcolor: transparent"]0.4[/TD]
[TD="width: 64, bgcolor: transparent"]5.6[/TD]
[TD="width: 64, bgcolor: transparent"]0.4[/TD]
[TD="width: 64, bgcolor: transparent"]2.8[/TD]
[TD="width: 64, bgcolor: transparent"]0[/TD]
[TD="width: 64, bgcolor: transparent"]2.3[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]nov[/TD]
[TD="width: 64, bgcolor: transparent"]-0.7[/TD]
[TD="width: 64, bgcolor: transparent"]3.1[/TD]
[TD="width: 64, bgcolor: transparent"]0.9[/TD]
[TD="width: 64, bgcolor: transparent"]5.7[/TD]
[TD="width: 64, bgcolor: transparent"]0.3[/TD]
[TD="width: 64, bgcolor: transparent"]2.3[/TD]
[TD="width: 64, bgcolor: transparent"]-0.5[/TD]
[TD="width: 64, bgcolor: transparent"]2.1[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]oct[/TD]
[TD="width: 64, bgcolor: transparent"]0[/TD]
[TD="width: 64, bgcolor: transparent"]4.9[/TD]
[TD="width: 64, bgcolor: transparent"]0.7[/TD]
[TD="width: 64, bgcolor: transparent"]5.1[/TD]
[TD="width: 64, bgcolor: transparent"]-0.8[/TD]
[TD="width: 64, bgcolor: transparent"]1.5[/TD]
[TD="width: 64, bgcolor: transparent"]0.2[/TD]
[TD="width: 64, bgcolor: transparent"]3.1[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]sep[/TD]
[TD="width: 64, bgcolor: transparent"]0[/TD]
[TD="width: 64, bgcolor: transparent"]5.5[/TD]
[TD="width: 64, bgcolor: transparent"]0.4[/TD]
[TD="width: 64, bgcolor: transparent"]3.5[/TD]
[TD="width: 64, bgcolor: transparent"]0.4[/TD]
[TD="width: 64, bgcolor: transparent"]2.5[/TD]
[TD="width: 64, bgcolor: transparent"]1[/TD]
[TD="width: 64, bgcolor: transparent"]3.2[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]aug[/TD]
[TD="width: 64, bgcolor: transparent"]-0.6[/TD]
[TD="width: 64, bgcolor: transparent"]6[/TD]
[TD="width: 64, bgcolor: transparent"]0.3[/TD]
[TD="width: 64, bgcolor: transparent"]3.5[/TD]
[TD="width: 64, bgcolor: transparent"]0.6[/TD]
[TD="width: 64, bgcolor: transparent"]3.2[/TD]
[TD="width: 64, bgcolor: transparent"]-0.6[/TD]
[TD="width: 64, bgcolor: transparent"]2.1[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]jul[/TD]
[TD="width: 64, bgcolor: transparent"]0[/TD]
[TD="width: 64, bgcolor: transparent"]6.9[/TD]
[TD="width: 64, bgcolor: transparent"]0[/TD]
[TD="width: 64, bgcolor: transparent"]3.8[/TD]
[TD="width: 64, bgcolor: transparent"]-0.2[/TD]
[TD="width: 64, bgcolor: transparent"]1.9[/TD]
[TD="width: 64, bgcolor: transparent"]0.7[/TD]
[TD="width: 64, bgcolor: transparent"]2.3[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]jun[/TD]
[TD="width: 64, bgcolor: transparent"]0.9[/TD]
[TD="width: 64, bgcolor: transparent"]7[/TD]
[TD="width: 64, bgcolor: transparent"]0.4[/TD]
[TD="width: 64, bgcolor: transparent"]3.6[/TD]
[TD="width: 64, bgcolor: transparent"]-0.3[/TD]
[TD="width: 64, bgcolor: transparent"]2.9[/TD]
[TD="width: 64, bgcolor: transparent"]0.1[/TD]
[TD="width: 64, bgcolor: transparent"]1.9[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]may[/TD]
[TD="width: 64, bgcolor: transparent"]1.1[/TD]
[TD="width: 64, bgcolor: transparent"]6.4[/TD]
[TD="width: 64, bgcolor: transparent"]1.2[/TD]
[TD="width: 64, bgcolor: transparent"]2.9[/TD]
[TD="width: 64, bgcolor: transparent"]0.9[/TD]
[TD="width: 64, bgcolor: transparent"]3.2[/TD]
[TD="width: 64, bgcolor: transparent"]0.3[/TD]
[TD="width: 64, bgcolor: transparent"]2.2[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]apr[/TD]
[TD="width: 64, bgcolor: transparent"]0.8[/TD]
[TD="width: 64, bgcolor: transparent"]6.5[/TD]
[TD="width: 64, bgcolor: transparent"]1.1[/TD]
[TD="width: 64, bgcolor: transparent"]2.6[/TD]
[TD="width: 64, bgcolor: transparent"]0.8[/TD]
[TD="width: 64, bgcolor: transparent"]2.7[/TD]
[TD="width: 64, bgcolor: transparent"]0[/TD]
[TD="width: 64, bgcolor: transparent"]2.7[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]mar[/TD]
[TD="width: 64, bgcolor: transparent"]1.3[/TD]
[TD="width: 64, bgcolor: transparent"]6.9[/TD]
[TD="width: 64, bgcolor: transparent"]1[/TD]
[TD="width: 64, bgcolor: transparent"]2.3[/TD]
[TD="width: 64, bgcolor: transparent"]0.8[/TD]
[TD="width: 64, bgcolor: transparent"]1.9[/TD]
[TD="width: 64, bgcolor: transparent"]1.1[/TD]
[TD="width: 64, bgcolor: transparent"]3.1[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]feb[/TD]
[TD="width: 64, bgcolor: transparent"]0[/TD]
[TD="width: 64, bgcolor: transparent"]6.5[/TD]
[TD="width: 64, bgcolor: transparent"]-0.2[/TD]
[TD="width: 64, bgcolor: transparent"]2.1[/TD]
[TD="width: 64, bgcolor: transparent"]0.4[/TD]
[TD="width: 64, bgcolor: transparent"]2.2[/TD]
[TD="width: 64, bgcolor: transparent"]0.6[/TD]
[TD="width: 64, bgcolor: transparent"]2.6[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]jan[/TD]
[TD="width: 64, bgcolor: transparent"]0.1[/TD]
[TD="width: 64, bgcolor: transparent"]6.4[/TD]
[TD="width: 64, bgcolor: transparent"]-0.7[/TD]
[TD="width: 64, bgcolor: transparent"]2.7[/TD]
[TD="width: 64, bgcolor: transparent"]-0.5[/TD]
[TD="width: 64, bgcolor: transparent"]2.4[/TD]
[TD="width: 64, bgcolor: transparent"]-0.6[/TD]
[TD="width: 64, bgcolor: transparent"]2.2[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]Table 2:[/TD]
[TD="width: 64, bgcolor: transparent"] [/TD]
[TD="width: 64, bgcolor: transparent"] [/TD]
[TD="width: 64, bgcolor: transparent"] [/TD]
[TD="width: 64, bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 128, bgcolor: transparent, colspan: 2"]Month/Year[/TD]
[TD="width: 192, bgcolor: transparent, colspan: 3"]Sum of monthly inflations for the last 12 months[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 128, bgcolor: transparent, colspan: 2"]4/1/2008[/TD]
[TD="width: 192, bgcolor: transparent, colspan: 3"]6.5[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, colspan: 2"]=SUM(OFFSET(A4,MATCH(TEXT(A19,"mmm"),$A$4:$A$15,0)-1,MATCH(YEAR(A19),$B$2:$I$2,0),MONTH(A19)))+SUM(OFFSET(A4,,MATCH(YEAR(A19),$B$2:$I$2,0)+2,12-MONTH(A19)))[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
A formula to provide you what you need is simple. The problem(to me) is the arrangement of your Table 1 Data.

If your data were in a simple 3 column format, like so:


Excel 2010
ABCDEFG
1
2DateMYMonth/YearSum of monthly inflations for the last 12 months
312/01/2008-0.62.104/30/20072.5
411/01/2008-0.73.1
510/01/200804.9
609/01/200805.5
708/01/2008-0.66
807/01/200806.9
906/01/20080.97
1005/01/20081.16.4
1104/01/20080.86.5
1203/01/20081.36.9
1302/01/200806.5
1401/01/20080.16.4
1512/01/20070.45.6
1611/01/20070.95.7
1710/01/20070.75.1
1809/01/20070.43.5
1908/01/20070.33.5
2007/01/200703.8
2106/01/20070.43.6
2205/01/20071.22.9
2304/01/20071.12.6
2403/01/200712.3
2502/01/2007-0.22.1
2601/01/2007-0.72.7
2712/01/20060.42.8
2811/01/20060.32.3
2910/01/2006-0.81.5
3009/01/20060.42.5
3108/01/20060.63.2
3207/01/2006-0.21.9
3306/01/2006-0.32.9
3405/01/20060.93.2
3504/01/20060.82.7
3603/01/20060.81.9
3702/01/20060.42.2
3801/01/2006-0.52.4
3912/01/200502.3
4011/01/2005-0.52.1
4110/01/20050.23.1
4209/01/200513.2
4308/01/2005-0.62.1
4407/01/20050.72.3
4506/01/20050.11.9
4605/01/20050.32.2
4704/01/200502.7
4803/01/20051.13.1
4902/01/20050.62.6
5001/01/2005-0.62.2
Sheet2
Cell Formulas
RangeFormula
F3=SUMPRODUCT(--($A$3:$A$50<=E3)*($A$3:$A$50>EOMONTH(E3,-12))*$B$3:$B$50)
 
Upvote 0
Hi guys!

Vogel, thank you very much for your solution. It works like a charm. I've been close to it myself, but couldn't get across the finishing line. Thanx!

Jim, thanks for the suggestion. I would also convert the table to a three column format, and make it easier, but the instructions on assignment "suggested" the format shouldn't be changed. Although I'm having some difficulty understanding your formula. Didn't know EOMONTH function till now, didn't know I could use SUMPRODUCT for something like that. Amazing. What does -- stand for in a function?

Sory guys for the different format of the numbers, but I don't come from Anglo-Saxon world and in Slovenia we use commas for decimals. HOpe it wasn't too much trouble.

Best regards, Marko.
 
Upvote 0
[TABLE="width: 1133"]
<colgroup><col width="64" style="width: 48pt;" span="9"> <col width="934" style="width: 701pt; mso-width-source: userset; mso-width-alt: 34157;"> <tbody>[TR]
[TD="width: 64, bgcolor: white"]Table 1:[/TD]
[TD="width: 64, bgcolor: white"]M[/TD]
[TD="width: 64, bgcolor: white"]Y[/TD]
[TD="width: 64, bgcolor: white"]M[/TD]
[TD="width: 64, bgcolor: white"]Y[/TD]
[TD="width: 64, bgcolor: white"]M[/TD]
[TD="width: 64, bgcolor: white"]Y[/TD]
[TD="width: 64, bgcolor: white"]M[/TD]
[TD="width: 64, bgcolor: white"]Y[/TD]
[TD="width: 934, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: white"]Year /[/TD]
[TD="width: 64, bgcolor: white"]2008[/TD]
[TD="width: 64, bgcolor: white"] [/TD]
[TD="width: 64, bgcolor: white"]2007[/TD]
[TD="width: 64, bgcolor: white"] [/TD]
[TD="width: 64, bgcolor: white"]2006[/TD]
[TD="width: 64, bgcolor: white"] [/TD]
[TD="width: 64, bgcolor: white"]2005[/TD]
[TD="width: 64, bgcolor: white"] [/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: white"]Month[/TD]
[TD="width: 64, bgcolor: white"] [/TD]
[TD="width: 64, bgcolor: white"] [/TD]
[TD="width: 64, bgcolor: white"] [/TD]
[TD="width: 64, bgcolor: white"] [/TD]
[TD="width: 64, bgcolor: white"] [/TD]
[TD="width: 64, bgcolor: white"] [/TD]
[TD="width: 64, bgcolor: white"] [/TD]
[TD="width: 64, bgcolor: white"] [/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: white"]dec[/TD]
[TD="width: 64, bgcolor: white"]-0.6[/TD]
[TD="width: 64, bgcolor: white"]2.1[/TD]
[TD="width: 64, bgcolor: white"]0.4[/TD]
[TD="width: 64, bgcolor: white"]5.6[/TD]
[TD="width: 64, bgcolor: white"]0.4[/TD]
[TD="width: 64, bgcolor: white"]2.8[/TD]
[TD="width: 64, bgcolor: white"]0[/TD]
[TD="width: 64, bgcolor: white"]2.3[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: white"]nov[/TD]
[TD="width: 64, bgcolor: white"]-0.7[/TD]
[TD="width: 64, bgcolor: white"]3.1[/TD]
[TD="width: 64, bgcolor: white"]0.9[/TD]
[TD="width: 64, bgcolor: white"]5.7[/TD]
[TD="width: 64, bgcolor: white"]0.3[/TD]
[TD="width: 64, bgcolor: white"]2.3[/TD]
[TD="width: 64, bgcolor: white"]-0.5[/TD]
[TD="width: 64, bgcolor: white"]2.1[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: white"]oct[/TD]
[TD="width: 64, bgcolor: white"]0[/TD]
[TD="width: 64, bgcolor: white"]4.9[/TD]
[TD="width: 64, bgcolor: white"]0.7[/TD]
[TD="width: 64, bgcolor: white"]5.1[/TD]
[TD="width: 64, bgcolor: white"]-0.8[/TD]
[TD="width: 64, bgcolor: white"]1.5[/TD]
[TD="width: 64, bgcolor: white"]0.2[/TD]
[TD="width: 64, bgcolor: white"]3.1[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: white"]sep[/TD]
[TD="width: 64, bgcolor: white"]0[/TD]
[TD="width: 64, bgcolor: white"]5.5[/TD]
[TD="width: 64, bgcolor: white"]0.4[/TD]
[TD="width: 64, bgcolor: white"]3.5[/TD]
[TD="width: 64, bgcolor: white"]0.4[/TD]
[TD="width: 64, bgcolor: white"]2.5[/TD]
[TD="width: 64, bgcolor: white"]1[/TD]
[TD="width: 64, bgcolor: white"]3.2[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: white"]aug[/TD]
[TD="width: 64, bgcolor: white"]-0.6[/TD]
[TD="width: 64, bgcolor: white"]6[/TD]
[TD="width: 64, bgcolor: white"]0.3[/TD]
[TD="width: 64, bgcolor: white"]3.5[/TD]
[TD="width: 64, bgcolor: white"]0.6[/TD]
[TD="width: 64, bgcolor: white"]3.2[/TD]
[TD="width: 64, bgcolor: white"]-0.6[/TD]
[TD="width: 64, bgcolor: white"]2.1[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: white"]jul[/TD]
[TD="width: 64, bgcolor: white"]0[/TD]
[TD="width: 64, bgcolor: white"]6.9[/TD]
[TD="width: 64, bgcolor: white"]0[/TD]
[TD="width: 64, bgcolor: white"]3.8[/TD]
[TD="width: 64, bgcolor: white"]-0.2[/TD]
[TD="width: 64, bgcolor: white"]1.9[/TD]
[TD="width: 64, bgcolor: white"]0.7[/TD]
[TD="width: 64, bgcolor: white"]2.3[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: white"]jun[/TD]
[TD="width: 64, bgcolor: white"]0.9[/TD]
[TD="width: 64, bgcolor: white"]7[/TD]
[TD="width: 64, bgcolor: white"]0.4[/TD]
[TD="width: 64, bgcolor: white"]3.6[/TD]
[TD="width: 64, bgcolor: white"]-0.3[/TD]
[TD="width: 64, bgcolor: white"]2.9[/TD]
[TD="width: 64, bgcolor: white"]0.1[/TD]
[TD="width: 64, bgcolor: white"]1.9[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: white"]may[/TD]
[TD="width: 64, bgcolor: white"]1.1[/TD]
[TD="width: 64, bgcolor: white"]6.4[/TD]
[TD="width: 64, bgcolor: white"]1.2[/TD]
[TD="width: 64, bgcolor: white"]2.9[/TD]
[TD="width: 64, bgcolor: white"]0.9[/TD]
[TD="width: 64, bgcolor: white"]3.2[/TD]
[TD="width: 64, bgcolor: white"]0.3[/TD]
[TD="width: 64, bgcolor: white"]2.2[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: white"]apr[/TD]
[TD="width: 64, bgcolor: white"]0.8[/TD]
[TD="width: 64, bgcolor: white"]6.5[/TD]
[TD="width: 64, bgcolor: white"]1.1[/TD]
[TD="width: 64, bgcolor: white"]2.6[/TD]
[TD="width: 64, bgcolor: white"]0.8[/TD]
[TD="width: 64, bgcolor: white"]2.7[/TD]
[TD="width: 64, bgcolor: white"]0[/TD]
[TD="width: 64, bgcolor: white"]2.7[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: white"]mar[/TD]
[TD="width: 64, bgcolor: white"]1.3[/TD]
[TD="width: 64, bgcolor: white"]6.9[/TD]
[TD="width: 64, bgcolor: white"]1[/TD]
[TD="width: 64, bgcolor: white"]2.3[/TD]
[TD="width: 64, bgcolor: white"]0.8[/TD]
[TD="width: 64, bgcolor: white"]1.9[/TD]
[TD="width: 64, bgcolor: white"]1.1[/TD]
[TD="width: 64, bgcolor: white"]3.1[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: white"]feb[/TD]
[TD="width: 64, bgcolor: white"]0[/TD]
[TD="width: 64, bgcolor: white"]6.5[/TD]
[TD="width: 64, bgcolor: white"]-0.2[/TD]
[TD="width: 64, bgcolor: white"]2.1[/TD]
[TD="width: 64, bgcolor: white"]0.4[/TD]
[TD="width: 64, bgcolor: white"]2.2[/TD]
[TD="width: 64, bgcolor: white"]0.6[/TD]
[TD="width: 64, bgcolor: white"]2.6[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: white"]jan[/TD]
[TD="width: 64, bgcolor: white"]0.1[/TD]
[TD="width: 64, bgcolor: white"]6.4[/TD]
[TD="width: 64, bgcolor: white"]-0.7[/TD]
[TD="width: 64, bgcolor: white"]2.7[/TD]
[TD="width: 64, bgcolor: white"]-0.5[/TD]
[TD="width: 64, bgcolor: white"]2.4[/TD]
[TD="width: 64, bgcolor: white"]-0.6[/TD]
[TD="width: 64, bgcolor: white"]2.2[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: white"]Table 2:[/TD]
[TD="width: 64, bgcolor: white"] [/TD]
[TD="width: 64, bgcolor: white"] [/TD]
[TD="width: 64, bgcolor: white"] [/TD]
[TD="width: 64, bgcolor: white"] [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Sumproduct could work, it's a little tricky with the months without changing the format."--"double negative converts the "TRUE" & "FALSE" into "1" & "0".[/TD]
[/TR]
[TR]
[TD="width: 128, bgcolor: white, colspan: 2"]Month/Year[/TD]
[TD="width: 192, bgcolor: white, colspan: 3"]Sum of monthly inflations for the last 12 months[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 128, bgcolor: white, colspan: 2"]4/1/2007[/TD]
[TD="width: 192, bgcolor: white, colspan: 3"]2.5[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]1. Choice[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]=SUM(OFFSET(A4,MATCH(TEXT(A19,"mmm"),$A$4:$A$15,0)-1,MATCH(YEAR(A19),$B$2:$I$2,0),MONTH(A19)))+SUM(OFFSET(A4,,MATCH(YEAR(A19),$B$2:$I$2,0)+2,12-MONTH(A19)))[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="width: 192, bgcolor: white, colspan: 3"]2.5[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]2. Choice[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]=SUMPRODUCT(($B$2:$H$2=YEAR(A19))*(({12;11;10;9;8;7;6;5;4;3;2;1})<=MONTH(A19))*B4:H15)+SUMPRODUCT(($B$2:$H$2=YEAR(A19)-1)*(({12;11;10;9;8;7;6;5;4;3;2;1})>MONTH(A19))*B4:H15)[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Thank you vogel for clearing the meaning of --, and explaining the methods used to make the functions work.
 
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top