SUMIF Returns a #VALUE error when external source is closed
 

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?

When you open that file, it should ask you if you want to update - choose "do
not update" otherwise you will have to open the other file too.

"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

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

SUMIF Returns a #VALUE error when external source is closed
 

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

SUMIF Returns a #VALUE error when external source is closed
 

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

SUMIF Returns a #VALUE error when external source is closed
 

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

SUMIF Returns a #VALUE error when external source is closed
 

Thanks for that - it works great.

By the way, what does the --(expession) syntax do?

"Domenic" wrote:

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

SUMIF Returns a #VALUE error when external source is closed
 

In article ,
"Gidders" wrote:

Thanks for that - it works great.

You're very welcome! Glad I could help!

By the way, what does the --(expession) syntax do?

Have a look at the following link...

http://www.mcgimpsey.com/excel/formulae/doubleneg.html