Conditional Sum For Multiple Sheets
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
|