Conditional Sum across WORKSHEET

[Domanda]

I have ten worksheets, named 1 to 10.
In worksheet 11 I have 10 cells, numbered 1 to 10, and for each of
them I write ON or OFF.
I know how to sum cell A1 in each worksheet, but I ignore how to have
the sum conditioned to the fact the worksheet is ON.
Something like:
If the worksheet is active/on, then you add cell A1 of that worksheet,
and check the remaining 9 worksheet. If the worksheet is OFF, you skip
it and do not consider in the sum.
Compared to the normal SUM.IF, the range to be checked is spread over
the 10 worksheet.

Hope it is clear
thanks in advance

 

[isabelle]

hi,

In worksheet 11, if the names are in column A and the on / off in column B

=IF(B1="on",INDIRECT(A1&"!A1"),0)
fill down

 

[Domanda]

hi,

In worksheet 11, if the names are in column A and the on / off in column B

=IF(B1="on",INDIRECT(A1&"!A1"),0)
fill down

Isabel....not what I asked I think.
I use this formula, but here I am asking to sum A1 in each worksheet
just using one cell.
I need a single formula making the calculations and check of
conditions ACROSS worksheets, not 10 formulas and then sum them up.

 

[isabelle]

hi Domanda,

I see no other solution than a custom Function

=MySum(A1:A10,A1)

Function MySum(MyRangeSheetsName As Range, OneRange As Range)
For Each rng In MyRangeSheetsName
MySum = MySum + Sheets(CStr(rng.Value)).Range(CStr(OneRange.Address))
Next
End Function

 

[bob]

hi Domanda,

I see no other solution than a custom Function

=MySum(A1:A10,A1)

Function MySum(MyRangeSheetsName As Range, OneRange As Range)
For Each rng In MyRangeSheetsName
MySum = MySum + Sheets(CStr(rng.Value)).Range(CStr(OneRange.Address))
Next
End Function

--
isabelle

Le 2011-04-21 13:20, Domanda a écrit :

Isabel....not what I asked I think.
I use this formula, but here I am asking to sum A1 in each worksheet
just using one cell.
I need a single formula making the calculations and check of
conditions ACROSS worksheets, not 10 formulas and then sum them up.- Hide quoted text -

- Show quoted text -

Hi Domanda,

If Isabell or someone knows how to make a sheet array =SUM(IF($A$1:$A
$10="on",1!A1:10!A1)) work, then it can be done in one cell. ELSE,
what I think you want to do Domanda, can be done in two steps. Enter
=SUM(IF($A$1:$A$10="on",1!$A1)) in Col [B1 to B10]. Fill down won't
work without changing 1to10 before "!$A1". Then simply do =sum(b1:b10)
in a cell for the total of cell "A1" in sheets selected with "ON".
Hope this helps, I tested it with three sheets without using a
volitile function like INDIRECT with it working fine.
INDIRECT works with filldown by designating sheets 1 through 10 in a
separate column and referencing it. Certainly there is a better way to
accomplish your goal, but this should get you by for now - unless I
goofed somewhere.

 

[Domanda]

Hi Domanda,

If Isabell or someone knows how to make a sheet array =SUM(IF($A$1:$A
$10="on",1!A1:10!A1)) work, then it can be done in one cell.

Hi Bob,
This is indeed what my original question was.

what I think you want to do Domanda, can be done in two steps. Enter
=SUM(IF($A$1:$A$10="on",1!$A1)) in Col [B1 to B10]. Fill down won't
work without changing 1to10 before "!$A1". Then simply do =sum(b1:b10)
in a cell for the total of cell "A1" in sheets selected with "ON".
Hope this helps, I tested it with three sheets without using a
volitile function like INDIRECT with it working fine.
INDIRECT works with filldown by designating sheets 1 through 10 in a
separate column and referencing it. Certainly there is a better way to
accomplish your goal, but this should get you by for now - unless I
goofed somewhere.

thanks a lot.
The point is I already solved the issue, with a two step procedure,
but I was wondering if there is a single shot to do it. Just for sake
of knowledge and curiosity.
This is the real problem.
I have 10 projects, which all together create a FUND. Projects are not
yet final, so i need to have ON/OFF (on the "Assumptions" worksheet)
and make some simulation to understand which is worth having or not.
I have the 10 worksheet, named 1 to 10, where I have only calculation
referring to that specific project. There are different sources of
revenues (REV 1, REV 2, REV3) and Cost (Cost 1, 2,3,4) and other items
for each projects.
Then I have FUND worksheet, where I do all the SUM but for the ACTIVE
projects only. I have the different Revenue lines and cost lines which
have to show the total, year by year, for the ON projects only. So I
needed a formula doing: go to worksheet 1, and if the project is ON,
then see how much is this revenue line during this year and go to
other 9 worksheets and check the same.
The only way I could make it was to create another worksheet for
"dirty calculations" and "gross summary": I created a table for each
Revenue line and each cost line. In each table I have the 10 project,
and the first column tells me if they are active or not. So now I can
-in my FUND page- use the Conditional SUM: if the criteria range is ON
(in the Summary sheet) then sum that year line of revenue for the ON
projects.

 

[isabelle]

hi all,

too bad, no way to do it with sumproduct,
because Excel doesn't accept this expression {'1'!A1, '2'!A1, '3'!A1} as being a matrix,
i doesn't see a way to do it, except with custom Function.

--
isabelle

Le 2011-04-23 05:21, bob a écrit :

hi Domanda,

I see no other solution than a custom Function

=MySum(A1:A10,A1)

Function MySum(MyRangeSheetsName As Range, OneRange As Range)
For Each rng In MyRangeSheetsName
MySum = MySum + Sheets(CStr(rng.Value)).Range(CStr(OneRange.Address))
Next
End Function

--
isabelle

Le 2011-04-21 13:20, Domanda a écrit :

In worksheet 11, if the names are in column A and the on / off in column B

=IF(B1="on",INDIRECT(A1&"!A1"),0)
fill down

Isabel....not what I asked I think.
I use this formula, but here I am asking to sum A1 in each worksheet
just using one cell.
I need a single formula making the calculations and check of
conditions ACROSS worksheets, not 10 formulas and then sum them up.- Hide quoted text -

- Show quoted text -

Hi Domanda,

If Isabell or someone knows how to make a sheet array =SUM(IF($A$1:$A
$10="on",1!A1:10!A1)) work, then it can be done in one cell. ELSE,
what I think you want to do Domanda, can be done in two steps. Enter
=SUM(IF($A$1:$A$10="on",1!$A1)) in Col [B1 to B10]. Fill down won't
work without changing 1to10 before "!$A1". Then simply do =sum(b1:b10)
in a cell for the total of cell "A1" in sheets selected with "ON".
Hope this helps, I tested it with three sheets without using a
volitile function like INDIRECT with it working fine.
INDIRECT works with filldown by designating sheets 1 through 10 in a
separate column and referencing it. Certainly there is a better way to
accomplish your goal, but this should get you by for now - unless I
goofed somewhere.