Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional summing across several sheets
I have a summary sheet which I need to summaries the results of various
projects on subsequent sheets. I wish be able to turn projects "on and off" in the summary. ie: Project Name Include (Y/N) Project 1 Y Project 2 Y Project 3 N Project 4 Y (For simplicity, the Project Name will be the same as its respective sheet name) On each project sheet, there is a total revenue row for each year of the project in columns. However some projects start later than others, and I require flexibility to move start dates. eg for each project on different sheets: Project 1 Year 2006 2007 2008 2009 2010 Revenue 500 200 400 300 500 Project 2 Year 2007 2008 2009 2010 2011 Revenue 500 200 400 300 500 Project 3 Year 2006 2007 2008 2009 2010 Revenue 500 200 400 300 500 Project 4 Year 2008 2009 2010 2011 2012 Revenue 500 200 400 300 500 Then on the Summary sheet, I should get the following (if project 3 is turned off): Summary Year 2006 2007 2008 2009 2010 Revenue 500 700 1100 900 1200 However, I want to be able to turn projects on and off at will. I have tried a few ways include array functions with little or no sucesss, can anybody help? Thanks. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional summing across several sheets
You can do it with SUMPRODUCT.
You need to create a list of projects and flags as you show and use that like so =SUMPRODUCT(SUMIF(INDIRECT("'"&$M1:$M4&"'!B1:F1"), B$1,INDIRECT("'"&$M1:$M4&" '!B2:F2"))*($N$1:$N$4="Y")) where M1:N4 is the name table, and B1:F2 is where the data on each sheet is held. I have created a small example to show it at http://cjoint.com/?iqkrQzRtlb -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Eastland" wrote in message ... I have a summary sheet which I need to summaries the results of various projects on subsequent sheets. I wish be able to turn projects "on and off" in the summary. ie: Project Name Include (Y/N) Project 1 Y Project 2 Y Project 3 N Project 4 Y (For simplicity, the Project Name will be the same as its respective sheet name) On each project sheet, there is a total revenue row for each year of the project in columns. However some projects start later than others, and I require flexibility to move start dates. eg for each project on different sheets: Project 1 Year 2006 2007 2008 2009 2010 Revenue 500 200 400 300 500 Project 2 Year 2007 2008 2009 2010 2011 Revenue 500 200 400 300 500 Project 3 Year 2006 2007 2008 2009 2010 Revenue 500 200 400 300 500 Project 4 Year 2008 2009 2010 2011 2012 Revenue 500 200 400 300 500 Then on the Summary sheet, I should get the following (if project 3 is turned off): Summary Year 2006 2007 2008 2009 2010 Revenue 500 700 1100 900 1200 However, I want to be able to turn projects on and off at will. I have tried a few ways include array functions with little or no sucesss, can anybody help? Thanks. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional summing across several sheets
Many thanks, that's tops.
"Bob Phillips" wrote: You can do it with SUMPRODUCT. You need to create a list of projects and flags as you show and use that like so =SUMPRODUCT(SUMIF(INDIRECT("'"&$M1:$M4&"'!B1:F1"), B$1,INDIRECT("'"&$M1:$M4&" '!B2:F2"))*($N$1:$N$4="Y")) where M1:N4 is the name table, and B1:F2 is where the data on each sheet is held. I have created a small example to show it at http://cjoint.com/?iqkrQzRtlb -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Eastland" wrote in message ... I have a summary sheet which I need to summaries the results of various projects on subsequent sheets. I wish be able to turn projects "on and off" in the summary. ie: Project Name Include (Y/N) Project 1 Y Project 2 Y Project 3 N Project 4 Y (For simplicity, the Project Name will be the same as its respective sheet name) On each project sheet, there is a total revenue row for each year of the project in columns. However some projects start later than others, and I require flexibility to move start dates. eg for each project on different sheets: Project 1 Year 2006 2007 2008 2009 2010 Revenue 500 200 400 300 500 Project 2 Year 2007 2008 2009 2010 2011 Revenue 500 200 400 300 500 Project 3 Year 2006 2007 2008 2009 2010 Revenue 500 200 400 300 500 Project 4 Year 2008 2009 2010 2011 2012 Revenue 500 200 400 300 500 Then on the Summary sheet, I should get the following (if project 3 is turned off): Summary Year 2006 2007 2008 2009 2010 Revenue 500 700 1100 900 1200 However, I want to be able to turn projects on and off at will. I have tried a few ways include array functions with little or no sucesss, can anybody help? Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Compare two Sheets with Conditional Formatting | Excel Discussion (Misc queries) | |||
Summing across multiple sheets with a twist | Excel Worksheet Functions | |||
calculating excel spreadsheet files for pensions and life insurance (including age calculation sheets) | Excel Worksheet Functions | |||
Can I use conditional formatting between two sheets to track chan. | Excel Discussion (Misc queries) | |||
Summing Sheets - SUMIF ? | Excel Worksheet Functions |