adding worksheets with conditions
 

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

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

Thanks Bernie,

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

Thanks

Niall

"Bernie Deitrick" wrote:

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

Niall,

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

HTH,
Bernie
MS Excel MVP


"Niall D" wrote in message
...
Thanks Bernie,

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

Thanks

Niall

"Bernie Deitrick" wrote:

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

Bernie,

Got it sorted.

Thanks

"Bernie Deitrick" wrote:

Niall,

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

HTH,
Bernie
MS Excel MVP


"Niall D" wrote in message
...
Thanks Bernie,

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

Thanks

Niall

"Bernie Deitrick" wrote:

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