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

Kevin

B

#### Bob Phillips

One way, a bit long winded but it works

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

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

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.

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)

W

#### wild turkey no9

Apologies for that. Newbie misunderstanding.

Kevin