XL2000 - Linking SUMIF with Other Workbook

[LPS]

I have two workbooks. One contains a detailed sheet and the other contains a
summary sheet. On the summary sheet I want to use SUMIF so that it goes to
the detail sheet and, if a specfiic condition exists in one column, it adds
the dollars in an other column, and brings the result back to the summary
sheet. When I set it up, both workbooks were open. When I tested it, I only
opened the summary sheet. All my otherlinks actively updated except those
using SUMIF. The SUMIF cells display #VALUE!, until I open the detail sheet.

Is this the way SUMIF is meant to work between workbooks or is this a "bug"
with SUMIF in XL2000 (O/S = XP)?

All help is greatly appreciated.

 

[Dave Peterson]

There are functions that don't work when the "sending" workbook is closed.

=sumif() and =indirect() are a couple.

But there are workarounds...

You could use =sumproduct()

Instead of using a formula like:

=SUMIF('C:\My Documents\Excel\[book2.xls]Sheet1'!$A$1:$A$36,"asdf",
'C:\My Documents\Excel\[book2.xls]Sheet1'!$B$1:$B$36)

You could use:
=SUMPRODUCT(--('C:\My Documents\Excel\[book2.xls]Sheet1'!$A$1:$A$36="asdf"),
'C:\My Documents\Excel\[book2.xls]Sheet1'!$B$1:$B$36)


Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

ps.

I would build the formula when the sending workbook is open. Let excel worry
about the syntax when the workbook is closed.

 

[Shane Devenshire]

Hi,

Although I would prefer SUMPRODUCT you can still use SUMIF (not really), for
example:

=SUM(IF('C:\Users\Shane\Documents\[10-21-2008.xls]Sheet2'!$B$8:$B$17="Dec",'C:\Users\Shane\Documents\[10-21-2008.xls]Sheet2'!$C$8:$C$17,""))

This SUM(IF function works with closed workbooks. It needs to be entered as
an array - that means press Shift+Ctrl+Enter to enter it, don't press just
Enter.

If this helps, please click the Yes button

Cheers,
Shane Devenshire