Excel 2007 Problem with SUMIF

[dcr]

In a worksheet I have one tab that contains detail information. I filter the
detail information twice and create a new worksheet a copy-paste the 2
different sorts. Then I return to the original worksheet, to a different tab,
and insert a SUMIF that references the newly created worksheet (Book1). The
process and SUMIF worked fine in Excel 2003 version. It also works fine if I
reference between 2 tabs in the same original worksheet.

(Note: If I don't create the new worksheet and just filter on the detail
tab, the SUMIF does not recognize the filter and pulls all the info from the
detail tab. That is why I create the new worksheet with the 2 filtered data
tabs.)

The formula in the original workbook looks like this:
SUMIF("'[Book1.xls]Sheet1'!$H:$H",B3,"'[Book1.xls]Sheet1'!$J:$J")

Since the original worksheet will be shared with users in a lower version,
although I am working in Excel 2007, I saved both worksheets to a lower
version (Excel 97-2003)

This seems to be a very simple and common SUMIF function. The error returned
is:

"The formula you typed contains an error" It sometimes highlights the B3
reference.

Thank you for assistance.

 

[Pete_UK]

SUMIF doesn't work with closed workbooks. Open Book1.xls first
(original name !!) and see if that clears up the problem.

Hope this helps.

Pete

 

[Dave Peterson]

And you surrounded the filenamed with double quotes ("). That means that excel
will treat them as text--not actual workbooks/worksheets/addresses.

But there are other functions that are equivalent with =sumif() and will work
when the sending workbook is closed:

=sumproduct(--('[Book1.xls]Sheet1'!$H:$H"=B3),'[Book1.xls]Sheet1'!$J:$J)

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

In a worksheet I have one tab that contains detail information. I filter the
detail information twice and create a new worksheet a copy-paste the 2
different sorts. Then I return to the original worksheet, to a different tab,
and insert a SUMIF that references the newly created worksheet (Book1). The
process and SUMIF worked fine in Excel 2003 version. It also works fine if I
reference between 2 tabs in the same original worksheet.

(Note: If I don't create the new worksheet and just filter on the detail
tab, the SUMIF does not recognize the filter and pulls all the info from the
detail tab. That is why I create the new worksheet with the 2 filtered data
tabs.)

The formula in the original workbook looks like this:
SUMIF("'[Book1.xls]Sheet1'!$H:$H",B3,"'[Book1.xls]Sheet1'!$J:$J")

Since the original worksheet will be shared with users in a lower version,
although I am working in Excel 2007, I saved both worksheets to a lower
version (Excel 97-2003)

This seems to be a very simple and common SUMIF function. The error returned
is:

"The formula you typed contains an error" It sometimes highlights the B3
reference.

Thank you for assistance.