I figured it out just as you responded...I've never used Dynamic ranges
before but they are fantastic!

"JB" wrote:

Use dynamic ranged for List (in column E):

=Offset($E$2,,,CountA($E:$E)-1)

JB

On 17 fév, 00:31, mitch wrote:
I am using a very similar function

=SUMPRODUCT(SUMIF((INDIRECT("'"&List&"'!A4:A35")), "Black",INDIRECT("'"&ListÂ*&"'!B4:B35")))

Where "List" is a specific list of worksheets. However "List" isn't
completely filled out, I am continually adding sheets and these are appended
to "List." This formula works, except for the cells without any value in
"List" excel returns a "REF! error. Is it possible to embed an ISERROR() or
IFERROR() into this function?



"JB" wrote:
For one condition only:

=SUMPRODUCT(SUMIF(INDIRECT("Branch"&ROW(INDIRECT(" 1:24"))&"!
B1:B100"),"Salt",INDIRECT("Branch"&ROW(INDIRECT("1 :24"))&"!D1:D100")))

JB
http://boisgontierjacques.free.fr

On 24 jan, 09:50, Shane Devenshire
wrote:
Hi,

SUMPRODUCT won't work against 3D range references such as Sheet1:Sheet5!A1
so your best choice would be a custom function or a relative long spreadsheet
formula.
--
If this helps, please click the Yes button

Cheers,
Shane Devenshire

"FARAZ QURESHI" wrote:
I have a (2007) workbook with around 25 Sheets in the following order:

Sheet1 named "Main"
Sheet2-Sheet25 named "Branch 1" to "Branch 24"

All the branch sheets are in the same structure with column B representing
the Product like "Salt", "Sugar" etc., column C representing the Product size
like "1/2 Kilo", "1 Kilo" etc. and finally the column D representing the
Amount of Sales.

Now I have the Covering Sheet in the following manner:
1/2 Kilo 1 Kilo 2 Kilo ... ... ...
Salt
Sugar
...
...
...

What sort of formula should be placed in the B2:Last so as to sum the
relevant data from ALL the sheets? Something like =SUMPRODUCT(--('Branch
1':'Branch 24'!B:B="Salt")*--('Branch 1':'Branch 24'!C:C="1/2 Kilo"),'Branch
1':'Branch 24'!D:D)

--

Best Regards,
FARAZ A. QURESHI- Masquer le texte des messages précédents -

- Afficher le texte des messages précédents -- Masquer le texte des messages précédents -

- Afficher le texte des messages précédents -