#VALUE! with SUMIF formula for linked workbooks 2

[robertsquestion]

Hi,

I'm using Excel 2000 on Windows 2000 and got the following question.
I have 2 workbooks:
1. Total Sales
2. Detailed Sales
In workbook 1 I'm using the following formula:

=SUMIF('[Detailed Sales.xls]Sheet1'!$A:$A;A2;'[Detailed Sales.xls]Sheet1'!$B:$B)

As long as I keep workbook 2 opened, this formula in workbook 1 works correct. But when I close both workbooks and only open workbook 1 (and click "Yes" to update linked data) the result of the formula shows : #VALUE!.

I tested other formula's with linked workbooks (for example "SUM"), but that worked ok. Both workbooks are created in Excel 2000.

I found another thread with almost the same problem (but then for Excel 2003). But there was no solution for the problem in this thread. The subject of this thread is "Excel 2003 Cells Returning #VALUE from linked files".

Do you know why the SUMIF formula doesn't seem to work with linked workbooks? Is there another solution?

Thanks a lot for your help!

Regards,
Robert van der Berg
The Netherlands

 

[xlbo]

check your link status (Edit>Links>Status) - some formulae just cannot update unless the other workbook is open

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question

 

[DPlank]

SUMIF and COUNTIF both require the remote workbook open, otherwise you'll get this problem.

It's possible to create a variation of SUMIF, by nesting the functions in the form

=SUM(IF(your formula, 1, 0))

Think this has to be entered as an array formula. Will try to dig up a reference on this.

Cheers,
Dave

Probably the only Test Analyst Manager on Tek-Tips...therefore whatever it was that went wrong, I'm to blame...

animadverto vos in Abyssus!

Take a look at Forum1393!

 

[GlennUK]

The formula will be:

=SUM(IF('[Detailed Sales.xls]Sheet1'!$A1:$A5000=A2,'[Detailed Sales.xls]Sheet1'!$B1:$B5000,0))

entered using Ctrl-Shift-Enter.

Important: please note, the references to the external book must not be entire columns.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[DPlank]

Well that saves me looking further, Glenn. Thanks! A star for your efforts.

Cheers,
Dave

Probably the only Test Analyst Manager on Tek-Tips...therefore whatever it was that went wrong, I'm to blame...

animadverto vos in Abyssus!

Take a look at Forum1393!

 

[robertsquestion]

Hi Glenn (and Dave),

I tried it, but I still get #VALUE! as formula-result. The formula looks like this now (so it's an Array):
{=SUMIF('[Detailed Sales.xls]Sheet1'!$A$2:$A$7;A2;'[Detailed Sales.xls]Sheet1'!$B$2:$B$7)}

Any idea what I'm doing wrong here?

Thanks,
Robert

 

[xlbo]

either ; is not the seperator you should be using or you may have a #VALUE! error somewhere in the dataset you are interrogating...

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question

 

[GlennUK]

Hi Robertsquestion, what you have, i.e.
{=SUMIF('[Detailed Sales.xls]Sheet1'!$A$2:$A$7;A2;'[Detailed Sales.xls]Sheet1'!$B$2:$B$7)}

is not what I said, i.e.
=SUM(IF('[Detailed Sales.xls]Sheet1'!$A1:$A5000=A2,'[Detailed Sales.xls]Sheet1'!$B1:$B5000,0))

which, if you change the references to match yours =
=SUM(IF('[Detailed Sales.xls]Sheet1'!$A2:$A7=A2,'[Detailed Sales.xls]Sheet1'!$B2:$B7,0))

Note: this is a SUM(IF( formula, not a SUMIF formula!


Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[robertsquestion]

Glenn,

Wow, works great, thanks a lot for your help!

Regards,
Robert