Multi Worksheet Sumproduct Excel 2003

[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

 

[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)

 

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

 

[wild turkey no9]

Bob

Worked like a charm. Many thanks.

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)

 

[wild turkey no9]

Apologies for that. Newbie misunderstanding.

Kevin