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

Glytch

New Member
Joined
Jan 14, 2014
Messages
12
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.
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,626
Office Version
  1. 365
Platform
  1. Windows
Please post a data sample with the relevant formulas using xl2bb (link below). Many users are unable (or unwilling) to download attached files.

 

Glytch

New Member
Joined
Jan 14, 2014
Messages
12
Example sumif.xlsb
ABCDEFGHIJKLMNOPQRSTU
1Period ReportingFeb
2MonthlyYear to date
3ActualBudgetPrior YearForecastActualBudgetPrior YearForecast
4
5Account 1 36.000Objective is
6Account 2How to index the formula to the account line instead of fixed area36.000User selects month in cell D1
7Account 3-72.000Values in collumn N are updated but
8Account 436.000-Variable range of collumns - ok
9Account 5-108.000- Assumes a fixed area and if accounts lines move on line below or up it brings thw wrong value
10Account 636.000
11Account 736.000
12Account 836.000
13Account 936.000
14Account 1072.000
15Account 1136.000
16Account 1236.000
17Account 1372.000
18Account 1436.000
19Account 1536.000
20Account 16How to index the formula to the account line instead of fixed area36.000
21Account 1736.000
22Account 1836.000
23Account 1936.000
24Account 2036.000
c) P&L Comparisions
Cell Formulas
RangeFormula
N18:N24,N15:N16,N10:N13,N8,N5:N6N5=IFERROR(SUM(Budget!A6:OFFSET(Budget!A6,0,MATCH(ReportPeriod,Budget!B$5:$M$5,0))),0)
N7N7=-SUM(N5:N6)
N9N9=N7-N8
N14,N17N14=SUM(N12:N13)
Named Ranges
NameRefers ToCells
ReportPeriod='c) P&L Comparisions'!$D$1N8, N10:N13, N15:N16, N18:N24, N5:N6
Cells with Data Validation
CellAllowCriteria
D1List=$AA$2:$AA$13
 

Glytch

New Member
Joined
Jan 14, 2014
Messages
12
Please post a data sample with the relevant formulas using xl2bb (link below). Many users are unable (or unwilling) to download attached files.

Thank you for the info. Sorry for the inconvinience.
 

Glytch

New Member
Joined
Jan 14, 2014
Messages
12

ADVERTISEMENT

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

Example sumif.xlsb
ABCDEFGHIJKLMNOPQRSTU
1Period ReportingFeb
2MonthlyYear to date
3ActualBudgetPrior YearForecastActualBudgetPrior YearForecast
4
5Account 1 36.000Objective is
6Account 2How to index the formula to the account line instead of fixed area36.000User selects month in cell D1
7Account 3-72.000Values in collumn N are updated but
8Account 436.000-Variable range of collumns - ok
9Account 5-108.000- Assumes a fixed area and if accounts lines move on line below or up it brings thw wrong value
10Account 636.000
11Account 736.000
12Account 836.000
13Account 936.000
14Account 1072.000
15Account 1136.000
16Account 1236.000
17Account 1372.000
18Account 1436.000
19Account 1536.000
20Account 16How to index the formula to the account line instead of fixed area36.000
21Account 1736.000
22Account 1836.000
23Account 1936.000
24Account 2036.000
c) P&L Comparisions
Cell Formulas
RangeFormula
N18:N24,N15:N16,N10:N13,N8,N5:N6N5=IFERROR(SUM(Budget!A6:OFFSET(Budget!A6,0,MATCH(ReportPeriod,Budget!B$5:$M$5,0))),0)
N7N7=-SUM(N5:N6)
N9N9=N7-N8
N14,N17N14=SUM(N12:N13)
Named Ranges
NameRefers ToCells
ReportPeriod='c) P&L Comparisions'!$D$1N8, N10:N13, N15:N16, N18:N24, N5:N6
Cells with Data Validation
CellAllowCriteria
D1List=$AA$2:$AA$13
 

Glytch

New Member
Joined
Jan 14, 2014
Messages
12
Example sumif.xlsb
ABCDEFGHIJKLM
1
2
3
4
5JulAugSepOctNovDecJanFebMarAprMayJun
6Account 1 1.0002.0003.0004.0005.0006.0007.0008.0009.00010.00011.00012.000
7Account 21.0002.0003.0004.0005.0006.0007.0008.0009.00010.00011.00012.000
8Account 31.0002.0003.0004.0005.0006.0007.0008.0009.00010.00011.00012.000
9Account 41.0002.0003.0004.0005.0006.0007.0008.0009.00010.00011.00012.000
10Account 51.0002.0003.0004.0005.0006.0007.0008.0009.00010.00011.00012.000
11Account 61.0002.0003.0004.0005.0006.0007.0008.0009.00010.00011.00012.000
12Account 71.0002.0003.0004.0005.0006.0007.0008.0009.00010.00011.00012.000
13Account 81.0002.0003.0004.0005.0006.0007.0008.0009.00010.00011.00012.000
Budget
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,626
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Jan 14, 2014
Messages
12
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

Well-known Member
Joined
Dec 30, 2008
Messages
11,626
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jan 14, 2014
Messages
12
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,119,060
Messages
5,575,869
Members
412,689
Latest member
nhsmedic
Top