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.

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

On Apr 21, 4:57*pm, dcr wrote:
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.

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 he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

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

dcr wrote:

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.

--

Dave Peterson