Change Links/SumIf function

[jillteresa]

Hi there,
I have a sumIF lookup adding tabs in another worksheet called 2006
Production Grid.

SUMIF('[2006 Production Grid 8.01.06.xls]Q1'!$A:$A,$B11,'[2006
Production Grid 8.01.06.xls]Q1'!I:I)+SUMIF('[2006 Production Grid
8.01.06.xls]Q2'!$A:$A,$B11,'[2006 Production Grid
8.01.06.xls]Q2'!I:I)+SUMIF('[2006 Production Grid
8.01.06.xls]Q3'!$A:$A,$B11,'[2006 Production Grid
8.01.06.xls]Q3'!I:I)+SUMIF('[2006 Production Grid
8.01.06.xls]Q4'!$A:$A,$B11,'[2006 Production Grid 8.01.06.xls]Q4'!I:I)

Unfortunately, the title of the Production Grid changes constantly to
reflect the date, so I need to update links and update the name of the
sheet for my formulas to work.

When I do this, all of my lookup formulas except my sumIF work. SumIF
only works if the Production Grid document is open.

Any idea why?
Jill

 

[Bob Phillips]

That's just the way it works. Try SUMPRODUCT

SUMPRODUCT(--('[2006 Production Grid 8.01.06.xls]Q1'!$A:$A=$B11),'[2006
Production Grid 8.01.06.xls]Q1'!I:I)

etc.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

 

[jillteresa]

Thanks for your help. I tried the formula and could not get it to work,
even when simplifying to one tab. Any other suggestions?
Jill

 

[Bob Phillips]

Sorry my mistake. SUMPRODUCT doesn't work with complete columns, you have to
specify a range.

SUMPRODUCT(--('[2006 Production Grid 8.01.06.xls]Q1'!$A1:$A100=$B11),'[2006
Production Grid 8.01.06.xls]Q1'!I1:I100)


--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

 

[jillteresa]

Thanks - this worked perfectly.