Dynamic Formulation with lots of sheets
 

I have an Excel workbok , that contains lots of sheets. Each have the same
structure.
Ex. data1 to datai and i is up to 100

Is it possible to write a formula like that ?

If datai'!A1=150 sum datai'B1

Sum all B1's of all sheets that has datai'!A1=150 condition.

Dynamic Formulation with lots of sheets
 

You can use

=SUM(Sheet2:Sheet3!B3)

to sume the values of B3 on multiple sheets, however, the condition
part will not work across worksheets. You may need to perform the
conditional check on the individual worksheets by placing the formula

IF(A3=150,+b3,0) in a blank cell and then use the formula above to sum
all of the cells that contain that formula.


--
Excelenator


------------------------------------------------------------------------
Excelenator's Profile: http://www.excelforum.com/member.php...o&userid=36768
View this thread: http://www.excelforum.com/showthread...hreadid=566624

Dynamic Formulation with lots of sheets
 

You can use Sumif to total your cells.
However, since it's a 3D configuration, you'll have to make a list of *all*
of the sheet names that you wish to add up.

This list must be *exact* matches to what's on the sheet tabs.

Since you said that you have 100 sheets or more, you must decide if this is
worth your while.

Say your list is in Column Z, from Z1 to Z100.

You can assign a name to this list, if you prefer, and use that name in your
formula.

Here's doing it both ways.

Assign name to list - say "list" (no quotes):

=SUMPRODUCT(SUMIF(INDIRECT("'"&list&"'!A1"),150,IN DIRECT("'"&list&"'!B1")))

Just using cell references:

=SUMPRODUCT(SUMIF(INDIRECT("'"&Z1:Z100&"'!A1"),150 ,INDIRECT("'"&Z1:Z100&"'!B1")))

Just make *sure* that with all that typing, your list is *accurate* compared
to the sheet tabs (spelling and spaces).

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"turrucan" wrote in message
...
I have an Excel workbok , that contains lots of sheets. Each have the same
structure.
Ex. data1 to datai and i is up to 100

Is it possible to write a formula like that ?

If datai'!A1=150 sum datai'B1

Sum all B1's of all sheets that has datai'!A1=150 condition.

Dynamic Formulation with lots of sheets
 

thanks very much ,

this is the formulation I need , and I have one more question.

If I want formula to sumproduct from different worksheets , how should I
modify this formula ?

Dynamic Formulation with lots of sheets
 

Did I miss something here?

This formula *is* for use on different worksheets!

If you mean *WorkBooks*, INDIRECT() does *not* work on closed WBs.
--

Regards,

RD
----------------------------------------------------------------------------
-------------------
Please keep all correspondence within the Group, so all may benefit !
----------------------------------------------------------------------------
-------------------

"turrucan" wrote in message
...
thanks very much ,

this is the formulation I need , and I have one more question.

If I want formula to sumproduct from different worksheets , how should I
modify this formula ?

Dynamic Formulation with lots of sheets
 

In your formula sumproduct formulation is used for summing over different
worksheets . When I need to sumproduct two variables B1 and C1 in these
sheets , should I use that formula or is there a shorter way ?

=SUMPRODUCT(SUMIF(INDIRECT("'"&F1:F100&"'!A1"),"15 0",INDIRECT("'"&F1:F100&"'!B1")),SUMIF(INDIRECT("' "&F1:F100&"'!A1"),"150",INDIRECT("'"&F1:F100&"'!c1 ")))