Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 11
Default Conditional Sum across WORKSHEET

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


  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 587
Default Conditional Sum across WORKSHEET

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

--
isabelle

Le 2011-04-20 17:32, Domanda a écrit :
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


  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 11
Default Conditional Sum across WORKSHEET

On Thu, 21 Apr 2011 07:22:58 -0400, isabelle wrote:

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.

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 587
Default Conditional Sum across WORKSHEET

hi Domanda,

I see no other solution than a custom Function

=MySum(A1:A10,A1)

Code:
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 :
On Thu, 21 Apr 2011 07:22:58 -0400, wrote:

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.

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
bob bob is offline
external usenet poster
 
Posts: 2
Default Conditional Sum across WORKSHEET

On Apr 21, 6:50*pm, isabelle wrote:
hi Domanda,

I see no other solution than a custom Function

=MySum(A1:A10,A1)

Code:
 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 :



On Thu, 21 Apr 2011 07:22:58 -0400, *wrote:


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.- 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.


  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 11
Default Conditional Sum across WORKSHEET

On Sat, 23 Apr 2011 02:21:44 -0700 (PDT), bob
wrote:


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.
  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 587
Default Conditional Sum across WORKSHEET

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 :
On Apr 21, 6:50 pm, wrote:
hi Domanda,

I see no other solution than a custom Function

=MySum(A1:A10,A1)

Code:
 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 :



On Thu, 21 Apr 2011 07:22:58 -0400, wrote:


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.- 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.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Conditional Copy from one worksheet to another John Duich Excel Worksheet Functions 1 April 19th 10 02:10 AM
Conditional formatting from one worksheet to another... kim Excel Discussion (Misc queries) 0 August 17th 07 01:48 AM
conditional summing from other worksheet nsword1478 Excel Worksheet Functions 3 April 25th 07 04:44 PM
Conditional Worksheet Formula Marie Setting up and Configuration of Excel 0 October 10th 06 07:50 AM
how can i set more than three conditional formats to xl worksheet Andy A112 Excel Worksheet Functions 2 August 9th 06 08:37 PM


All times are GMT +1. The time now is 09:48 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"