Excel 2003 Multi Worksheet Sumproduct?
 

I just cannot solve this one. Any help you can give is much appreciated......

There are 12 sheets labled Jan, Feb Mar etc. Each sheet has the following

Col A - Name
Col C - Data to be summed

On a summary sheet I would like to able to do the following : -

For a particular month, say May, and a particular Name, sum all of the Col C
data
for the preceeding months (in this case from worksheets Jan to April).

Thanks in advance

Kevin

Excel 2003 Multi Worksheet Sumproduct?
 

Hi

One way
Create 3 new sheets called Summary, First and Last
Drag them so that you have the order Summary, First, Jan Feb, Mar, Last, Apr
..... Dec
On Summary, Copy your list of names from Column A of Jan, and paste to
Column A of Summary
In C1 enter Values
Enter in C2
=IF(A2="","",SUM(First:Last!C2))
Copy formula down as far as required

On Summary SheetDataAutofilter
Use the dropdown on Column A to Select Name required

Drag the last tab to any other position to change the range of months
totaled.
Equally, you can drag first to a different location if you wanted to total
between Feb and Jun for example.
If you always want to start from Jan, having located First between Summary
and Jan, you could hide the sheet and just have last viewable to drag to the
required position.
--
Regards
Roger Govier

"wild turkey no9" wrote in message
...
I just cannot solve this one. Any help you can give is much
appreciated......

There are 12 sheets labled Jan, Feb Mar etc. Each sheet has the following

Col A - Name
Col C - Data to be summed

On a summary sheet I would like to able to do the following : -

For a particular month, say May, and a particular Name, sum all of the Col
C
data
for the preceeding months (in this case from worksheets Jan to April).

Thanks in advance

Kevin

Excel 2003 Multi Worksheet Sumproduct?
 

Hi Roger

An very cool solution indeed! One key thing I forgot to mention (my bad) is
that the name order may vary from sheet to sheet, and the names may be
present in some sheets but not others - hence my original thought at somehow
trying a pseudo 3D lookup.....

"Roger Govier" wrote:

Hi

One way
Create 3 new sheets called Summary, First and Last
Drag them so that you have the order Summary, First, Jan Feb, Mar, Last, Apr
.... Dec
On Summary, Copy your list of names from Column A of Jan, and paste to
Column A of Summary
In C1 enter Values
Enter in C2
=IF(A2="","",SUM(First:Last!C2))
Copy formula down as far as required

On Summary SheetDataAutofilter
Use the dropdown on Column A to Select Name required

Drag the last tab to any other position to change the range of months
totaled.
Equally, you can drag first to a different location if you wanted to total
between Feb and Jun for example.
If you always want to start from Jan, having located First between Summary
and Jan, you could hide the sheet and just have last viewable to drag to the
required position.
--
Regards
Roger Govier

"wild turkey no9" wrote in message
...
I just cannot solve this one. Any help you can give is much
appreciated......

There are 12 sheets labled Jan, Feb Mar etc. Each sheet has the following

Col A - Name
Col C - Data to be summed

On a summary sheet I would like to able to do the following : -

For a particular month, say May, and a particular Name, sum all of the Col
C
data
for the preceeding months (in this case from worksheets Jan to April).

Thanks in advance

Kevin

Excel 2003 Multi Worksheet Sumproduct?
 

How would you determine what months to include? Will you type month name in
a cell? Pick it from a drop down list? Why don't you just start with the
month you're interested in?

You said if the month is May then sum Jan:Apr. Why not just select/enter Apr
and then sum Jan:Apr ?

Or, might you want the sum based on a year to date basis excluding the
current month?



--
Biff
Microsoft Excel MVP


"wild turkey no9" wrote in message
...
Hi Roger

An very cool solution indeed! One key thing I forgot to mention (my bad)
is
that the name order may vary from sheet to sheet, and the names may be
present in some sheets but not others - hence my original thought at
somehow
trying a pseudo 3D lookup.....

"Roger Govier" wrote:

Hi

One way
Create 3 new sheets called Summary, First and Last
Drag them so that you have the order Summary, First, Jan Feb, Mar, Last,
Apr
.... Dec
On Summary, Copy your list of names from Column A of Jan, and paste to
Column A of Summary
In C1 enter Values
Enter in C2
=IF(A2="","",SUM(First:Last!C2))
Copy formula down as far as required

On Summary SheetDataAutofilter
Use the dropdown on Column A to Select Name required

Drag the last tab to any other position to change the range of months
totaled.
Equally, you can drag first to a different location if you wanted to
total
between Feb and Jun for example.
If you always want to start from Jan, having located First between
Summary
and Jan, you could hide the sheet and just have last viewable to drag to
the
required position.
--
Regards
Roger Govier

"wild turkey no9" wrote in
message
...
I just cannot solve this one. Any help you can give is much
appreciated......

There are 12 sheets labled Jan, Feb Mar etc. Each sheet has the
following

Col A - Name
Col C - Data to be summed

On a summary sheet I would like to able to do the following : -

For a particular month, say May, and a particular Name, sum all of the
Col
C
data
for the preceeding months (in this case from worksheets Jan to April).

Thanks in advance

Kevin

Excel 2003 Multi Worksheet Sumproduct?
 

Well, I see you have a solution at your other post!

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
How would you determine what months to include? Will you type month name
in a cell? Pick it from a drop down list? Why don't you just start with
the month you're interested in?

You said if the month is May then sum Jan:Apr. Why not just select/enter
Apr and then sum Jan:Apr ?

Or, might you want the sum based on a year to date basis excluding the
current month?



--
Biff
Microsoft Excel MVP


"wild turkey no9" wrote in
message ...
Hi Roger

An very cool solution indeed! One key thing I forgot to mention (my bad)
is
that the name order may vary from sheet to sheet, and the names may be
present in some sheets but not others - hence my original thought at
somehow
trying a pseudo 3D lookup.....

"Roger Govier" wrote:

Hi

One way
Create 3 new sheets called Summary, First and Last
Drag them so that you have the order Summary, First, Jan Feb, Mar, Last,
Apr
.... Dec
On Summary, Copy your list of names from Column A of Jan, and paste to
Column A of Summary
In C1 enter Values
Enter in C2
=IF(A2="","",SUM(First:Last!C2))
Copy formula down as far as required

On Summary SheetDataAutofilter
Use the dropdown on Column A to Select Name required

Drag the last tab to any other position to change the range of months
totaled.
Equally, you can drag first to a different location if you wanted to
total
between Feb and Jun for example.
If you always want to start from Jan, having located First between
Summary
and Jan, you could hide the sheet and just have last viewable to drag to
the
required position.
--
Regards
Roger Govier

"wild turkey no9" wrote in
message
...
I just cannot solve this one. Any help you can give is much
appreciated......

There are 12 sheets labled Jan, Feb Mar etc. Each sheet has the
following

Col A - Name
Col C - Data to be summed

On a summary sheet I would like to able to do the following : -

For a particular month, say May, and a particular Name, sum all of the
Col
C
data
for the preceeding months (in this case from worksheets Jan to April).

Thanks in advance

Kevin