Lookup and Sum

[ciara_daniels]

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.

 

[Toppers]

With your data starting in B2 (for KPI A) in sheet "Budget" then in your
summary "CUM YTD" for "KPI A" put:


=SUM(OFFSET(INDIRECT("B" &MATCH(A2,Budget!$A$1:$A$100,0)),0,0,1,$X$1))

X1 contains number of months
A2 contains "KPI A"
B2 will have cum. ytd target for KPI A

HTH

 

[Ciara]

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 you 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.
TIA

 

[Toppers]

Can you send me your s/sheet and I'll look at it;it's easier to see with the
actual data. I'm in the UK and it's getting late here so I may not be able
to look until morning UK time.

But try changing the $A$7 to $A$1.

toppers<at>johntopley.fsnet.co.uk

 

[Toppers]

Hi,
I have tried to emulate your worksheets using your formula and I
get an answer of 20 for 10 columns each containing 2. It wasn't clear to me
on which sheet the summary was but there appears to be three sheets - SUMMARY
(where the formula is) ,'Targets KPI Sheet' and 'Front Sheet KPI Statistics'

=SUM(OFFSET(INDIRECT("'Targets KPI Sheet'!E"&MATCH(C16,'Targets KPI
Sheet'!$A$1:$A$110,0)),0,0,1,'Front Sheet KPI Statistics'!$G$5))


Note that i added <'Targets KPI Sheet'!> in front of the E as this data must
be on the same sheets the MATCH data 'Targets KPI Sheet'!$A$1:$A$110.

Offer to send me a w/book is still open!

HTH

 

[Ciara]

Thanks a million, it works perfectly. I can now stop pulling my hair
out!!!

 

[Toppers]

Glad to hear it's OK and thank you for the feedback.