Multi Worksheet Sumproduct Excel 2003


W

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
 
Ad

Advertisements

B

Bob Phillips

One way, a bit long winded but it works

Say the target month is in H1, target name in H2.

Add these formulae

I1: =MONTH(DATEVALUE("01-"&H1))
I2:
=IF(DATE(2008,ROW(A1),1)>DATEVALUE("01-"&$H$1),"",TEXT(DATE(2008,ROW(A1),1),"mmm"))

copy I2 down to I13, or just put a list Jan,Feb,Mar,... in I2:I13

then the result formula is

=SUMPRODUCT(SUMIF(INDIRECT(I2:INDEX(I2:I13,I1)&"!A1:A30"),H2,INDIRECT(I2:INDEX(I2:I13,I1)&"!B1:B30")))



--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
R

Roger Govier

see response to your posting in woksheet.functions

Please do not multipost, as it wastes a lot of time as people do not know
that you have already received an answer.
 
W

wild turkey no9

Bob

Worked like a charm. Many thanks.

Bob Phillips said:
One way, a bit long winded but it works

Say the target month is in H1, target name in H2.

Add these formulae

I1: =MONTH(DATEVALUE("01-"&H1))
I2:
=IF(DATE(2008,ROW(A1),1)>DATEVALUE("01-"&$H$1),"",TEXT(DATE(2008,ROW(A1),1),"mmm"))

copy I2 down to I13, or just put a list Jan,Feb,Mar,... in I2:I13

then the result formula is

=SUMPRODUCT(SUMIF(INDIRECT(I2:INDEX(I2:I13,I1)&"!A1:A30"),H2,INDIRECT(I2:INDEX(I2:I13,I1)&"!B1:B30")))



--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top