Try using SUMPRODUCT...

=SUMPRODUCT(--('C:\Documents and Settings\1358745\My Documents\[CSS
Input.xls]Input'!$T3:$T151="Yes"),'C:\Documents and Settings\1358745\My
Documents\[CSS Input.xls]Input'!$C3:$C151)/'C:\Documents and
Settings\1358745\My Documents\[CSS Input.xls]Input'!$V$2

Note the your ranges need to be the same size.

Hope this helps!

In article ,
"Gidders" wrote:

I'm having the same issue. I have one sheet which contains the data I want to
sum, dependant on the data held in another sheet, in this case [CSS Input] eg

=SUMIF('[CSS Input.xls]Input'!$T3:$T151,"yes",C11:C151)/'[CSS
Input.xls]Input'!$V$2

If the sheet CSS Input is open at the same time as the sheet I'm working on
then the formula works. If however I just open the main file, I'm propmted to
update the links to external data sources & if I click update (because CSS
Input might have been amended independently), it returns #value.

The formula now reads formula reads

=SUMIF('C:\Documents and Settings\1358745\My Documents\[CSS
Input.xls]Input'!$T3:$T151,"yes",C11:C151)/'C:\Documents and
Settings\1358745\My Documents\[CSS Input.xls]Input'!$V$2

I've never had problems with other links to external sheets and in fact the
straight reference to [CSS Input.xls]Input'!$V$2 works fine

Any ideas?
Gidders