Dynamic Formulation with lots of sheets

T

turrucan

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

Excelenator

You can use

=SUM(Sheet2:Sheet3!B3)

to sume the values of B3 on multiple sheets, however, the conditio
part will not work across worksheets. You may need to perform th
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 su
all of the cells that contain that formula
 
R

Ragdyer

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,INDIRECT("'"&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).
 
T

turrucan

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 ?
 
R

RagDyeR

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

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 ?
 
T

turrucan

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"),"150",INDIRECT("'"&F1:F100&"'!B1")),SUMIF(INDIRECT("'"&F1:F100&"'!A1"),"150",INDIRECT("'"&F1:F100&"'!c1")))
 
Top