Change Links/SumIf function

  • Thread starter Thread starter jillteresa
  • Start date Start date
J

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
 
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)
 
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
:eek:
 
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)
 
Back
Top