Lookup and Sum
 

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.

Lookup and Sum
 

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

" wrote:

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.

Lookup and Sum
 

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 wrote:

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

" wrote:

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.

Lookup and Sum
 

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<atjohntopley.fsnet.co.uk

"Ciara" wrote:

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 wrote:

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

" wrote:

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.

Lookup and Sum
 

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" wrote:

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 wrote:

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

" wrote:

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.

Lookup and Sum
 

Thanks a million, it works perfectly. I can now stop pulling my hair
out!!!
C
Toppers wrote:
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" wrote:

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 wrote:

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

" wrote:

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.

Lookup and Sum
 

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

"Ciara" wrote:

Thanks a million, it works perfectly. I can now stop pulling my hair
out!!!
C
Toppers wrote:
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" wrote:

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 wrote:

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

" wrote:

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.