Multi Worksheet Sumproduct Excel 2003
 

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

Multi Worksheet Sumproduct Excel 2003
 

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)&"!A 1: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" 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

Multi Worksheet Sumproduct Excel 2003
 

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.

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

Multi Worksheet Sumproduct Excel 2003
 

Bob

Worked like a charm. Many thanks.

"Bob Phillips" wrote:

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)&"!A 1: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" 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

Multi Worksheet Sumproduct Excel 2003
 

Apologies for that. Newbie misunderstanding.

Kevin

"Roger Govier" wrote:

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.

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