Conditional Sum For Multiple Sheets

[FARAZ QURESHI]

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)

 

[Shane Devenshire]

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.

 

[JB]

For one condition only:

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

JB
http://boisgontierjacques.free.fr

 

[T. Valko]

Whether this is practical or not is for the user to decide!

For multiple SUMPRODUCT conditions across multiple sheets...

All Branch n sheets are structured as:

B2:B10 = product (text)
C2:C10 = size (text)
D210 = numbers to sum (numeric)

Summary sheet:

A2 = some product name
B1 = some size

Create these named expressions:

Insert>Name>Defne
Name: Array1
Refers to: =COLUMN(INDIRECT("A:X"))

This evaluates to an array from 1 to 24 which corresponds to your sheet
names: Branch 1 to Branch 24

Name: Array2
Refers to: =ROW(INDIRECT("2:10"))

This is used for the OFFSET in defining the range address

Then this formula (all on one line) to sum D210 across all the sheets
where B2:B10=A2 and C2:C10=B1:

Breaking it into "chunks" so that line wrap doesn't take out the space
characters after each instance of Branch.

=SUMPRODUCT(--(T(OFFSET(INDIRECT("'BRANCH "
&Array1&"'!B2:B10"),Array2-2,,))=A2),
--(T(OFFSET(INDIRECT("'Branch "&Array1&"'!C2:C10")
,Array2-2,,))=B1),N(OFFSET(INDIRECT("'Branch "
&Array1&"'!D210"),Array2-2,,)))


--
Biff
Microsoft Excel MVP


For one condition only:

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

JB
http://boisgontierjacques.free.fr

 

[FARAZ QURESHI]

WOW!!!!
Biff XCLENT!!!!
By the way...

1. Why doesn't the normal text, instead of name, work? &
2. What if the size is in numerical terms instead of text?

THANK YOU VERY VERY MUCH!

 

[T. Valko]

1. Why doesn't the normal text, instead of name, work?

Are you referring to Array1 and Array2? I used those names to shorten the
overall length of the formula.

2. What if the size is in numerical terms instead of text?

Then you'd change the T function for that array to the N function.

Whatever data type you're testing for in a particular array determines which
of those functions to use:

Arrays that are TEXT use the T function
Arrays that are NUMERIC use the N function

 

[mitch]

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]

Use dynamic ranged for List (in column E):

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

JB

 

[mitch]

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