Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|