Suming values from two columns

M

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]
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Sort by date Sort by votes
[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
You must log in or register to reply here.

Similar threads

H
  • Solved
Color 3 cells.
Replies
4
Views
99
harzer
H
M
  • Solved
Formula for numbers of distribution as per %...into specific roundup
2
Replies
11
Views
922
motilulla
M
S
  • Question Question
Automatically get the x and y values to calculate a SLOPE
2
Replies
16
Views
2K
DILIPandey
DILIPandey
J
  • Question Question
Finding and summing an interval in a table
Replies
0
Views
493
Julianohcosta
J
R
  • Question Question
can this be solved with excel?
Replies
1
Views
445
r_ivica
R

Forum statistics

Threads
1,223,910
Messages
6,175,318
Members
452,634
Latest member
cpostell

Share this page

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