Niall,

You need to change your workbook structu 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


"Niall D" <Niall wrote in message
...
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