Excel 2003 Multi Worksheet Sumproduct?

[wild turkey no9]

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

 

[Roger Govier]

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 Sheet>Data>Autofilter
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.

 

[wild turkey no9]

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.....

 

[T. Valko]

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?

 

[T. Valko]

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