I cant seem to get this to work. It's giving me the wrong answers and I

cant work out what it's doing wrong.

I have used formula
SUM(OFFSET(INDIRECT("E"&MATCH(C16,'Targets KPI
sheet'!$A$7:$A$110,0)),0,0,1,'Front Sheet KPI Statistics'!$G$5))


"E" is the column on the budget sheet where the monthly data starts
$g$5 contains the number of months on the summary sheet
c16 contains the "kpi A"
E16 is where I've entered the formula on the summary sheet
$A$7:$A$110 is the list of the KPIs


Is it obvious what Im doing wrong? The result should be 20 for month 10

as I have the value "2" in columns 1-10. I keep getting 15.

MY ORIGINAL POST BELOW
Hi
I have a budget sheet with the following

KPI Apr May Jun Jul
KPI A 20 21 25 27
KPI B 44 22 33 22


I then have a summary sheet with the following
Report Month : (user enters report month number)


KPI Cum. YTD Target Cum. Actual Annual
Target
KPI A (formula A) (formula B)
(formula C)
KPI B (formula A) (formula B)
(formula C)


What I need is formula A.I would like to be able to get the cumulative
YTD total based on the report month entered by the user.
For example if the user enters 2 as the report month, then the cum YTD
total for KPI A should show as 20+21=41.


How can I achieve this? I have thought of using sumif, but I cant work
out how to combine it with a lookup or maybe there is a better way to
achieve the result.



TIA

Hi!

There's nothing wrong with your formula so it's hard to say what the problem
might be.

Maybe you need to offset by 1 column or you need to adjust the MATCH value
(+/-1) ???

Biff

wrote in message
oups.com...
I cant seem to get this to work. It's giving me the wrong answers and I

cant work out what it's doing wrong.

I have used formula
SUM(OFFSET(INDIRECT("E"&MATCH(C16,'Targets KPI
sheet'!$A$7:$A$110,0)),0,0,1,'Front Sheet KPI Statistics'!$G$5))


"E" is the column on the budget sheet where the monthly data starts
$g$5 contains the number of months on the summary sheet
c16 contains the "kpi A"
E16 is where I've entered the formula on the summary sheet
$A$7:$A$110 is the list of the KPIs


Is it obvious what Im doing wrong? The result should be 20 for month 10

as I have the value "2" in columns 1-10. I keep getting 15.

MY ORIGINAL POST BELOW
Hi
I have a budget sheet with the following

KPI Apr May Jun Jul
KPI A 20 21 25 27
KPI B 44 22 33 22


I then have a summary sheet with the following
Report Month : (user enters report month number)


KPI Cum. YTD Target Cum. Actual Annual
Target
KPI A (formula A) (formula B)
(formula C)
KPI B (formula A) (formula B)
(formula C)


What I need is formula A.I would like to be able to get the cumulative
YTD total based on the report month entered by the user.
For example if the user enters 2 as the report month, then the cum YTD
total for KPI A should show as 20+21=41.


How can I achieve this? I have thought of using sumif, but I cant work
out how to combine it with a lookup or maybe there is a better way to
achieve the result.



TIA