Conditional summing across several sheets

[Guest]

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.

 

[Bob Phillips]

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)

 

[Guest]

Many thanks, that's tops.

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)