YTD sum based on variable cell (sumif offset, index and match)

[Glytch]

Hello all,

I am running into a problem with a formula. So far i got it to work summing a YTD value based on a index & match function but i get one issue.
It retrieves the rante total that are in page "Budget" but are is fixed. So if lines are not in that order or we add lines the values retrived are incorrect.

How can i index the account name in collumn C sheet "c) P&L Comparisions" to sum if name is equal + current formula summing YTD range based on input of month in cell D1

Attached the example that has the working formula... i guess its easier to explain
File Example

Any help is much appreciated. I guess i spent over 4 days banging my head on this issue through multiple aproaches and documentation and nothing so far.

 

[jasonb75]

Please post a data sample with the relevant formulas using xl2bb (link below). Many users are unable (or unwilling) to download attached files.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

 

[Glytch]

Example sumif.xlsb
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1			Period Reporting	Feb
2				Monthly									Year to date
3				Actual	Budget	Prior Year	Forecast						Actual	Budget	Prior Year	Forecast
4
5			Account 1											36.000				Objective is
6			Account 2				How to index the formula to the account line instead of fixed area							36.000				User selects month in cell D1
7			Account 3											-72.000				Values in collumn N are updated but
8			Account 4											36.000				-Variable range of collumns - ok
9			Account 5											-108.000				- Assumes a fixed area and if accounts lines move on line below or up it brings thw wrong value
10			Account 6											36.000
11			Account 7											36.000
12			Account 8											36.000
13			Account 9											36.000
14			Account 10											72.000
15			Account 11											36.000
16			Account 12											36.000
17			Account 13											72.000
18			Account 14											36.000
19			Account 15											36.000
20			Account 16				How to index the formula to the account line instead of fixed area							36.000
21			Account 17											36.000
22			Account 18											36.000
23			Account 19											36.000
24			Account 20											36.000
c) P&L Comparisions

Cell Formulas
Range	Formula
N18:N24,N15:N16,N10:N13,N8,N5:N6	N5	=IFERROR(SUM(Budget!A6:OFFSET(Budget!A6,0,MATCH(ReportPeriod,Budget!B$5:$M$5,0))),0)
N7	N7	=-SUM(N5:N6)
N9	N9	=N7-N8
N14,N17	N14	=SUM(N12:N13)

Named Ranges
Name	Refers To	Cells
ReportPeriod	='c) P&L Comparisions'!$D$1	N8, N10:N13, N15:N16, N18:N24, N5:N6

Cells with Data Validation
Cell	Allow	Criteria
D1	List	=$AA$2:$AA$13

 

[Glytch]

Please post a data sample with the relevant formulas using xl2bb (link below). Many users are unable (or unwilling) to download attached files.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

Thank you for the info. Sorry for the inconvinience.

 

[Glytch]

I have a sheet Budget were data will be stored for sum.

Example sumif.xlsb
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1			Period Reporting	Feb
2				Monthly									Year to date
3				Actual	Budget	Prior Year	Forecast						Actual	Budget	Prior Year	Forecast
4
5			Account 1											36.000				Objective is
6			Account 2				How to index the formula to the account line instead of fixed area							36.000				User selects month in cell D1
7			Account 3											-72.000				Values in collumn N are updated but
8			Account 4											36.000				-Variable range of collumns - ok
9			Account 5											-108.000				- Assumes a fixed area and if accounts lines move on line below or up it brings thw wrong value
10			Account 6											36.000
11			Account 7											36.000
12			Account 8											36.000
13			Account 9											36.000
14			Account 10											72.000
15			Account 11											36.000
16			Account 12											36.000
17			Account 13											72.000
18			Account 14											36.000
19			Account 15											36.000
20			Account 16				How to index the formula to the account line instead of fixed area							36.000
21			Account 17											36.000
22			Account 18											36.000
23			Account 19											36.000
24			Account 20											36.000
c) P&L Comparisions

Cell Formulas
Range	Formula
N18:N24,N15:N16,N10:N13,N8,N5:N6	N5	=IFERROR(SUM(Budget!A6:OFFSET(Budget!A6,0,MATCH(ReportPeriod,Budget!B$5:$M$5,0))),0)
N7	N7	=-SUM(N5:N6)
N9	N9	=N7-N8
N14,N17	N14	=SUM(N12:N13)

Named Ranges
Name	Refers To	Cells
ReportPeriod	='c) P&L Comparisions'!$D$1	N8, N10:N13, N15:N16, N18:N24, N5:N6

Cells with Data Validation
Cell	Allow	Criteria
D1	List	=$AA$2:$AA$13

 

[Glytch]

Example sumif.xlsb
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2
3
4
5		Jul	Aug	Sep	Oct	Nov	Dec	Jan	Feb	Mar	Apr	May	Jun
6	Account 1	1.000	2.000	3.000	4.000	5.000	6.000	7.000	8.000	9.000	10.000	11.000	12.000
7	Account 2	1.000	2.000	3.000	4.000	5.000	6.000	7.000	8.000	9.000	10.000	11.000	12.000
8	Account 3	1.000	2.000	3.000	4.000	5.000	6.000	7.000	8.000	9.000	10.000	11.000	12.000
9	Account 4	1.000	2.000	3.000	4.000	5.000	6.000	7.000	8.000	9.000	10.000	11.000	12.000
10	Account 5	1.000	2.000	3.000	4.000	5.000	6.000	7.000	8.000	9.000	10.000	11.000	12.000
11	Account 6	1.000	2.000	3.000	4.000	5.000	6.000	7.000	8.000	9.000	10.000	11.000	12.000
12	Account 7	1.000	2.000	3.000	4.000	5.000	6.000	7.000	8.000	9.000	10.000	11.000	12.000
13	Account 8	1.000	2.000	3.000	4.000	5.000	6.000	7.000	8.000	9.000	10.000	11.000	12.000
Budget

 

[jasonb75]

Only had a quick glance, I think I can see what you need, I'm not going to be able to look at it it in detail today. I'll take another look later / tomorrow when I get chance. Possibly another member will look at the thread and provide an answer for you before that.

 

[Glytch]

Only had a quick glance, I think I can see what you need, I'm not going to be able to look at it it in detail today. I'll take another look later / tomorrow when I get chance. Possibly another member will look at the thread and provide an answer for you before that.

Thank you.
Hopefully i was able to explain well on the example and the attachment with the current formulas.
Your help is much appreciated. I can wait, maybe the wall will be more damaged in the end

 

[jasonb75]

See if this works, the formula is set for the data in the Budget sheet to end at row 20 (adjust as needed).

I notice from your examples that you appear to be using a dot / period as a thousand separator, if that is the case then you will most likely need to change the commas in the formula to semi-colons.

=IFERROR(SUM(INDEX(Budget!$B$6:INDEX(Budget!$B$6:$M$20,0,MATCH(ReportPeriod,Budget!$B$5:$M$5,0)),MATCH($C5,Budget!$A$6:$A$20,0),0)),"")

 

[Glytch]

See if this works, the formula is set for the data in the Budget sheet to end at row 20 (adjust as needed).

I notice from your examples that you appear to be using a dot / period as a thousand separator, if that is the case then you will most likely need to change the commas in the formula to semi-colons.

=IFERROR(SUM(INDEX(Budget!$B$6:INDEX(Budget!$B$6:$M$20,0,MATCH(ReportPeriod,Budget!$B$5:$M$5,0)),MATCH($C5,Budget!$A$6:$A$20,0),0)),"")

Amazing Jasonb75
This is working perfectly. Thank you very much for the time and support.
PS: thank you for the tip on how to share the data properly.