Vlookup+sumifs in a Moving Trend Analysis

[nymyth]

Hi all, as usual I need some experts help on an excel formula issue I have. I tried to use different formulas to get it working, but nothing I do is really working.

In the attached file I have 2 sheets:
Sheet1 - Data Dump
Sheet 2 - Analysis

In Sheet 1 only the data will change, the rows and columns will be identical (other than the years on Row 1) - so Periods # - 12 for Last Year and # - 12 for Current Year will always be where they are.

In Sheet 2 Cell P2 will have the Current Month and Year depending on a variable on a data sheet (not included here). So if the data sheet has the month as May 2016, cell P2 will read May 2016, if the data sheet has the month as April 2016, cell P2 will read April 2016 - and the previous cells will automatically adjust subtracting one month from the prior month until I have a full cycle. So if the variable is May 2016 my cells will read May 2015 to May 2016.

Now what I require help with is the following, in Cell D5 I need it to find Current Assets in Sheet 1 and sum from C11:H11. In Cell E5 from C11:I11 and so on. Then we arrive at Sheet 2 Cell L5 i need it sum from P11:Q11 and so on.

It get complex here because those dates will change every month and I need the formula to also adapt as the months go by. Is this possible? If so, can someone help? Thanks again.

Sheet 1

A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB
	Calendar Year	2015	2015	2015	2015	2015	2015	2015	2015	2015	2015	2015	2015	2015	2016	2016	2016	2016	2016	2016	2016	2016	2016	2016	2016	2016	2016
Period	#	1	2	3	4	5	6	7	8	9	10	11	12	#	1	2	3	4	5	6	7	8	9	10	11	12
BALANCE SHEET	Balance sheet	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
1000000000	Assets	580	930	930	580	776	986	853	923	608	979	986	853	986	853	608	608	608	608	608	0	0	0	0	0	0	0
1100000000	Non current assets	260	460	460	260	372	492	416	456	276	488	492	416	492	416	276	276	276	276	276	0	0	0	0	0	0	0
1110000000	Property, plant and equipment	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100	100
1120000000	Intangible assets	40	90	90	40	68	98	79	89	44	97	98	79	98	79	44	44	44	44	44
1130000000	Investments in Associates & Non consolidated joint ventures	40	90	90	40	68	98	79	89	44	97	98	79	98	79	44	44	44	44	44
1140000000	Other non current investments	40	90	90	40	68	98	79	89	44	97	98	79	98	79	44	44	44	44	44
1150000000	Deferred tax assets	40	90	90	40	68	98	79	89	44	97	98	79	98	79	44	44	44	44	44
1200000000	Current assets	320	470	470	320	404	494	437	467	332	491	494	437	494	437	332	332	332	332	332	0	0	0	0	0	0	0

<tbody>
</tbody>

Sheet 2

A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
			May 2015	Jun 2015	Jul 2015	Aug 2015	Sep 2015	Oct 2015	Nov 2015	Dec 2015	Jan 2016	Feb 2016	Mar 2016	Apr 2016	May 2016
			2015	2015	2015	2015	2015	2015	2015	2015	2016	2016	2016	2016	2016
			5	6	7	8	9	10	11	12	1	2	3	4	5
	1200000000	Current assets
	1210000000	Inventories
	1000000000	Assets
	2220000000	Current liabilities
	2200000000	Liabilities
	2100000000	Equity

<tbody>
</tbody>

TinyUpload.com - best file hosting solution, with no limits, totaly free

 

[sdruley]

06.25.2016-21.57.01 - sdruley's library

If you arrange you data this way you can use a pivot table to get your results on one sheet. Hope this helps

 

[nymyth]

Hi Sd, thank you for your response, unfortunately I cannot rearrange the data as that is how it comes out of our system. However, I did workout a solution, it probably isn't the prettiest but it did the trick:

Here is the formula

=(SUMIFS(INDEX(BSLvL4,MATCH(TEXT($B9,0),BSAccountsLvL4,0),0),CalYear,F$5,BSPeriodsLvL4,"<="&F$6)+SUMIFS(INDEX(BSLvL4,MATCH(TEXT($B9,0),BSAccountsLvL4,0),0),CalYear,F$5,BSPeriodsLvL4,"="&"#"))

BSLvL4 = range of all my data
BSAccountsLvL4 = range of my accounts
CalYear = range of my years
BSPeriodsLvL4 = range of my periods

=(SUMIFS(INDEX(BSLvL4,MATCH(TEXT($B9,0),BSAccountsLvL4,0),0),CalYear,F$5,BSPeriodsLvL4,"<="&F$6))

$B9 is pointing to my first account
F$5 is pointing to the year in that column
F$6 is pointing to the month in that column

=(SUMIFS(INDEX(BSLvL4,MATCH(TEXT($B9,0),BSAccountsLvL4,0),0),CalYear,F$5,BSPeriodsLvL4,"="&"#"))

Pretty much the same as above except the last part of the code changes to add the opening balance which is referenced by "#" in my data dump sheet

 

[Aladin Akyurek]

Does this single formula set up work for you?

=SUMPRODUCT(SUMIFS(INDEX(BSLvL4,MATCH(TEXT($B9,0),BSAccountsLvL4,0),0),
    CalYear,F$5,BSPeriodsLvL4,CHOOSE({1,2},"<="&F$6,"="&"#")))

 

[nymyth]

Holy hell, that worked perfectly. Any chance you can explain what you did there?

 

[nymyth]

Totally forgot to thank you for that code, so "thanks"

Does this single formula set up work for you?

=SUMPRODUCT(SUMIFS(INDEX(BSLvL4,MATCH(TEXT($B9,0),BSAccountsLvL4,0),0),
    CalYear,F$5,BSPeriodsLvL4,CHOOSE({1,2},"<="&F$6,"="&"#")))

 

[Aladin Akyurek]

Holy hell, that worked perfectly. Any chance you can explain what you did there?

CHOOSE collects the evaluations of its arguments into an array-constant.

Let's say that F6 = 1-Mar-2016.

CHOOSE then yields:

{"<=1-Mar-2016","#"}

which is a two criterion set that must hold BSPeriodsLvL4.

SUMIFS proceeds and creates two totals corresponding to each criterion in this set...

=SUMPRODUCT({15,30})

>> 45

That is, SumProduct totals the two totals.

Hope this helps.

Totally forgot to thank you for that code, so "thanks"

You are welcome.