Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Use Sum() with variable range
Hi, I have a sales report which needs to sum year-to-date sales by state.
Right now, the information is sitting in a pivot table linked to a live database... so while my sales report may be for February, the pivot table is also reporting sales up to the current date (which is often a week or two into the next month). That means I cannot use the "sales total" reported in the pivot table... because it will include sales for the current month which is not part of the report. So what I'm doing now, is linking a "table" to the pivot report via the SUM() formula. Every month I drag the sum formula down one more row for EACH STATE! Is there not a way that I could have the argument of the SUM() function defined such that if the month is March, then the summed range is defined as the first cell in the range + the next two cells below... e.g., I could have a table with each month equal to an integer (Jan, 0, Feb 1, Mar, 2, Apr, 3) and perhaps use VLOOKUP() to pull in the correct integer by which to grow the range. This may be confusing. I'm sorry if it is. If there is an entirely easier way to do this please let me know that as well. AND the reason I'm not just going into the Pivot Table to restrict it to the current month is that there are 3 pivot tables per report and 4 reports (one for each sale representative... and growing)... and I don't want to have to go in and change each pivot table each month... This is also the first set of several reports that I'm working on... and I'm trying to make them as automatic as I can. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Use Sum() with variable range
does SUMPRODUCT do what you want.
the syntax : =SUMPRODUCT(--(A2:A30="State"),--(B2:B30="Month"),C2:C30) -- Hope this is helpful Pls click the Yes button below if this post provide answer you have asked Thank You cheers, francis Am not a greek but an ordinary user trying to assist another "Jeff" wrote: Hi, I have a sales report which needs to sum year-to-date sales by state. Right now, the information is sitting in a pivot table linked to a live database... so while my sales report may be for February, the pivot table is also reporting sales up to the current date (which is often a week or two into the next month). That means I cannot use the "sales total" reported in the pivot table... because it will include sales for the current month which is not part of the report. So what I'm doing now, is linking a "table" to the pivot report via the SUM() formula. Every month I drag the sum formula down one more row for EACH STATE! Is there not a way that I could have the argument of the SUM() function defined such that if the month is March, then the summed range is defined as the first cell in the range + the next two cells below... e.g., I could have a table with each month equal to an integer (Jan, 0, Feb 1, Mar, 2, Apr, 3) and perhaps use VLOOKUP() to pull in the correct integer by which to grow the range. This may be confusing. I'm sorry if it is. If there is an entirely easier way to do this please let me know that as well. AND the reason I'm not just going into the Pivot Table to restrict it to the current month is that there are 3 pivot tables per report and 4 reports (one for each sale representative... and growing)... and I don't want to have to go in and change each pivot table each month... This is also the first set of several reports that I'm working on... and I'm trying to make them as automatic as I can. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Use Sum() with variable range
If each pivot has the same field names and is to show the same single month,
you could put an event monitor on the selected date cell in the first pivot, then change the other pivots to have the same values. -- __________________________________ HTH Bob "Jeff" wrote in message ... Hi, I have a sales report which needs to sum year-to-date sales by state. Right now, the information is sitting in a pivot table linked to a live database... so while my sales report may be for February, the pivot table is also reporting sales up to the current date (which is often a week or two into the next month). That means I cannot use the "sales total" reported in the pivot table... because it will include sales for the current month which is not part of the report. So what I'm doing now, is linking a "table" to the pivot report via the SUM() formula. Every month I drag the sum formula down one more row for EACH STATE! Is there not a way that I could have the argument of the SUM() function defined such that if the month is March, then the summed range is defined as the first cell in the range + the next two cells below... e.g., I could have a table with each month equal to an integer (Jan, 0, Feb 1, Mar, 2, Apr, 3) and perhaps use VLOOKUP() to pull in the correct integer by which to grow the range. This may be confusing. I'm sorry if it is. If there is an entirely easier way to do this please let me know that as well. AND the reason I'm not just going into the Pivot Table to restrict it to the current month is that there are 3 pivot tables per report and 4 reports (one for each sale representative... and growing)... and I don't want to have to go in and change each pivot table each month... This is also the first set of several reports that I'm working on... and I'm trying to make them as automatic as I can. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Use Sum() with variable range
Hi Bob, thanks for the suggestion, but I'm not sure I understand what an
"event monitor" is. Would you be able to explain? Is that a macro function? Also, the pivot report isn't showing just a single month... it looks like this: 2007 2008 2009 State1 Jan xxx.xx xxx.xx xxx.xx Feb xxx.xx xxx.xx xxx.xx Mar xxx.xx xxx.xx xxx.xx ... to December State2 Jan xxx.xx xxx.xx xxx.xx Feb... to December I have five states. So, say the month is March. (That's the report I'm working on right now.) I need to be able to report comparative monthly sales by state (e.g., March of 2007, '08, '09), and YTD '07, '08, '09. So, what I'm doing is summing Jan, Feb, & Mar... for '07, '08, & '09 FOR EACH STATE (for the YTD report). I use that to create a table and a chart for a dashboard. So I drag the SUM() formula for each year and each state down one more row (15 times) (this is just one section of the dashboard). I hope this clarifies... "Bob Phillips" wrote: If each pivot has the same field names and is to show the same single month, you could put an event monitor on the selected date cell in the first pivot, then change the other pivots to have the same values. -- __________________________________ HTH Bob "Jeff" wrote in message ... Hi, I have a sales report which needs to sum year-to-date sales by state. Right now, the information is sitting in a pivot table linked to a live database... so while my sales report may be for February, the pivot table is also reporting sales up to the current date (which is often a week or two into the next month). That means I cannot use the "sales total" reported in the pivot table... because it will include sales for the current month which is not part of the report. So what I'm doing now, is linking a "table" to the pivot report via the SUM() formula. Every month I drag the sum formula down one more row for EACH STATE! Is there not a way that I could have the argument of the SUM() function defined such that if the month is March, then the summed range is defined as the first cell in the range + the next two cells below... e.g., I could have a table with each month equal to an integer (Jan, 0, Feb 1, Mar, 2, Apr, 3) and perhaps use VLOOKUP() to pull in the correct integer by which to grow the range. This may be confusing. I'm sorry if it is. If there is an entirely easier way to do this please let me know that as well. AND the reason I'm not just going into the Pivot Table to restrict it to the current month is that there are 3 pivot tables per report and 4 reports (one for each sale representative... and growing)... and I don't want to have to go in and change each pivot table each month... This is also the first set of several reports that I'm working on... and I'm trying to make them as automatic as I can. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Use Sum() with variable range
Hi Francis, and thanks for the response. That might work, and looks very
interesting... what are the "--" arguments???? But, I'm not really wanting to multiply across columns. Below is the pivot table: Column A.....Column B..... Column C...... Column D.... Column E State............Month............2007 sales....2008 sales...2009 sales So, the first twelve (12) rows = state 1 .......the second 12 rows = state 2.... and so on. So, I need to generate two tables from this data: 1st - month comparison year on year (i.e., march of '07, '08, & '09) 2nd - YTD comparison year on year (i.e., YTD '07, '08, & '09) So, for this second table, every month, I'm having to add one more month to the SUM() function to get the YTD sales: for March 2007 YTD the formula would be, SUM(C1:C3) for April 2007 YTD the formula would be, SUM(C1:C4) for May 2007 YTD the formula would be, SUM(C1:C5) and so on. I have to repeat this for 2008, and 2009 (columns D and E) and I have to repeat it for each state. I'm not sure if this helps or if this makes it more confusing... thanks again. "Francis" wrote: does SUMPRODUCT do what you want. the syntax : =SUMPRODUCT(--(A2:A30="State"),--(B2:B30="Month"),C2:C30) -- Hope this is helpful Pls click the Yes button below if this post provide answer you have asked Thank You cheers, francis Am not a greek but an ordinary user trying to assist another "Jeff" wrote: Hi, I have a sales report which needs to sum year-to-date sales by state. Right now, the information is sitting in a pivot table linked to a live database... so while my sales report may be for February, the pivot table is also reporting sales up to the current date (which is often a week or two into the next month). That means I cannot use the "sales total" reported in the pivot table... because it will include sales for the current month which is not part of the report. So what I'm doing now, is linking a "table" to the pivot report via the SUM() formula. Every month I drag the sum formula down one more row for EACH STATE! Is there not a way that I could have the argument of the SUM() function defined such that if the month is March, then the summed range is defined as the first cell in the range + the next two cells below... e.g., I could have a table with each month equal to an integer (Jan, 0, Feb 1, Mar, 2, Apr, 3) and perhaps use VLOOKUP() to pull in the correct integer by which to grow the range. This may be confusing. I'm sorry if it is. If there is an entirely easier way to do this please let me know that as well. AND the reason I'm not just going into the Pivot Table to restrict it to the current month is that there are 3 pivot tables per report and 4 reports (one for each sale representative... and growing)... and I don't want to have to go in and change each pivot table each month... This is also the first set of several reports that I'm working on... and I'm trying to make them as automatic as I can. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Variable Range | Excel Discussion (Misc queries) | |||
Variable Range for NPV | Excel Worksheet Functions | |||
how to specify a range that is variable. | Excel Worksheet Functions | |||
from Range variable | Excel Discussion (Misc queries) | |||
from Range variable | Charts and Charting in Excel |