Excel 2003 Multi Worksheet Sumproduct?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

wild turkey no9 - 17 May 2008 06:34 GMT

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

Reply to this Message

Roger Govier - 17 May 2008 09:37 GMT

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.

Signature

Regards
Roger Govier

> I just cannot solve this one. Any help you can give is much
> appreciated......
[quoted text clipped - 14 lines]
>
> Kevin

Reply to this Message

wild turkey no9 - 17 May 2008 12:47 GMT

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

> Hi
>
[quoted text clipped - 37 lines]
> >
> > Kevin

Reply to this Message

T. Valko - 17 May 2008 18:03 GMT

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?

Signature

Biff
Microsoft Excel MVP

> Hi Roger
>
[quoted text clipped - 52 lines]
>> >
>> > Kevin

Reply to this Message

T. Valko - 17 May 2008 18:10 GMT

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

Signature

Biff
Microsoft Excel MVP

> 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
[quoted text clipped - 62 lines]
>>> >
>>> > Kevin

Reply to this Message