adding worksheets with conditions

[Guest]

Hi,

I have a workbook with 50 sheets I am trying to add and summarize the
product sales by country and week number. Each sheet has the same lay out.
The two variables are country and week number

Each sheet is country specific, however they are not grouped together, ie
one after the other as the data is summarized on a week to week basis.

I need a formula to go to each sheet check if it meets the conditions and
add this to the next sheet and so on to give me the total.

I have a global summary for all weeks ie total sales, but this is done in
each sheet by summary by country in each worksheet and adding the result
together.

Example
Week # 9 Country USA
Product
A
B
C
D
E

Has anyone any ideas? I have tried SUMIF, SUMPRODUCT, VLOOKUP

 

[Bernie Deitrick]

Niall,

You need to change your workbook structure: multiple sheets are often a bad
design.

Combine the table from all the sheets together onto one sheet, adding a
column that identifies the whatever it was that is unique to the old sheets.
When you have everything on one sheet, you can either use SUBTOTAL formulas
combined with filters to give you your results, or use a Pivot Table (the
prefered method).

HTH,
Bernie
MS Excel MVP

 

[Guest]

Thanks Bernie,

THe worksheets are too big to combine onto one sheet. I will have to try
something else.

Thanks

Niall

 

[Bernie Deitrick]

Niall,

You could store the data in a database, and still use Excel's Pivot Table
functionality.

HTH,
Bernie
MS Excel MVP

 

[Guest]

Bernie,

Got it sorted.

Thanks

Niall,

You could store the data in a database, and still use Excel's Pivot Table
functionality.

HTH,
Bernie
MS Excel MVP