SUMIF does not work with closed workbooks, you need to use SUMPRODUCT

=SUMPRODUCT(--(Range="yes"),SumRange)




--

Regards,

Peo Sjoblom

"Gidders" wrote in message
...
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




"FSt1" wrote:

hi,
post your sumif formula. need more info that you profided. we are not
psychics.

Regards
FSt1


"ghynes" wrote:


Can you help me out with this? if works fine if the external source is
open but returns a #value if its closed. is there anyway of correcting
this?


--
ghynes