To ensure that linkages in my spreadsheet will work for other users on our
shared folders, I have used the full pathname, or UNC name, in my formulas.
It works fine for VLOOKUP, but for some reason I get the #VALUE! error when
using SUMIF in Excel 2007. I'm not sure what I'm doing incorrectly, or
simply that using the UNC name will not work in SUMIF.

Here's a sample of the syntax:

SUMIF('\\hyperdisk\data\accounting and finance\budgets\2008 CFO
Report\[PriorActual.xlsm]January'!$F$2:$F$99999,$B42&$C42,'\\hyperdisk\data \accounting
and finance\budgets\2008 CFO Report\[PriorActual.xlsm]January'!$G$2:$G$99999)

SUMIF won't work if the source file is closed. Use SUMPRODUCT.

=SUMPRODUCT(--(pathJanuary'!$F$2:$F$99999=$B42&$C42),pathJanuary '!$G$2:$G$99999)

--
Biff
Microsoft Excel MVP


"Bruce" wrote in message
...
To ensure that linkages in my spreadsheet will work for other users on our
shared folders, I have used the full pathname, or UNC name, in my
formulas.
It works fine for VLOOKUP, but for some reason I get the #VALUE! error
when
using SUMIF in Excel 2007. I'm not sure what I'm doing incorrectly, or
simply that using the UNC name will not work in SUMIF.

Here's a sample of the syntax:

SUMIF('\\hyperdisk\data\accounting and finance\budgets\2008 CFO
Report\[PriorActual.xlsm]January'!$F$2:$F$99999,$B42&$C42,'\\hyperdisk\data \accounting
and finance\budgets\2008 CFO
Report\[PriorActual.xlsm]January'!$G$2:$G$99999)

It's not the UNC path that's the problem. The problem is that =sumif() won't
work when the sending file is closed.

But there are alternatives.

One of them is =sumproduct()

=sumproduct(--(longstring!$f$2:$f$99999=$b42&$c42),longstring!$g $2:$g$99999)

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:

=========
ps. I'd open the sending workbook before I created the formula. When I have it
correct and close that workbook excel will include the path.

pps. Open the file via the UNC path, too.

Bruce wrote:

To ensure that linkages in my spreadsheet will work for other users on our
shared folders, I have used the full pathname, or UNC name, in my formulas.
It works fine for VLOOKUP, but for some reason I get the #VALUE! error when
using SUMIF in Excel 2007. I'm not sure what I'm doing incorrectly, or
simply that using the UNC name will not work in SUMIF.

Here's a sample of the syntax:

SUMIF('\\hyperdisk\data\accounting and finance\budgets\2008 CFO
Report\[PriorActual.xlsm]January'!$F$2:$F$99999,$B42&$C42,'\\hyperdisk\data \accounting
and finance\budgets\2008 CFO Report\[PriorActual.xlsm]January'!$G$2:$G$99999)

--

Dave Peterson

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

(missed the URL)

Dave Peterson wrote:

It's not the UNC path that's the problem. The problem is that =sumif() won't
work when the sending file is closed.

But there are alternatives.

One of them is =sumproduct()

=sumproduct(--(longstring!$f$2:$f$99999=$b42&$c42),longstring!$g $2:$g$99999)

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:

=========
ps. I'd open the sending workbook before I created the formula. When I have it
correct and close that workbook excel will include the path.

pps. Open the file via the UNC path, too.

Bruce wrote:

To ensure that linkages in my spreadsheet will work for other users on our
shared folders, I have used the full pathname, or UNC name, in my formulas.
It works fine for VLOOKUP, but for some reason I get the #VALUE! error when
using SUMIF in Excel 2007. I'm not sure what I'm doing incorrectly, or
simply that using the UNC name will not work in SUMIF.

Here's a sample of the syntax:

SUMIF('\\hyperdisk\data\accounting and finance\budgets\2008 CFO
Report\[PriorActual.xlsm]January'!$F$2:$F$99999,$B42&$C42,'\\hyperdisk\data \accounting
and finance\budgets\2008 CFO Report\[PriorActual.xlsm]January'!$G$2:$G$99999)

--

Dave Peterson

--

Dave Peterson

Thanks for your help, both Biff and Dave!

The SUMIF only worked when I opened the source file by using the whole path
before building the SUMIF formula, as you have suggested. This is the less
desirable solution, since in order to edit a formula, one would always have
to remember to open the source file, and the full path is not displayed in
the formula box when I hit the check mark while that file is open. But I had
to explore this option since the users here are familiar with SUMIF and not
SUMPRODUCT.

I tried the SUMPRODUCT formula which was much better. The double negative
(--) looks kind of funny and would be hard to explain to my users, so I used
the multiply (*) operator instead and got the same results. Both McGimpsey
and Phillips were very helpful in explaining how this all works. Phillips
especially gave a few different ways you could go at this.

"Dave Peterson" wrote:

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

(missed the URL)

Dave Peterson wrote:

It's not the UNC path that's the problem. The problem is that =sumif() won't
work when the sending file is closed.

But there are alternatives.

One of them is =sumproduct()

=sumproduct(--(longstring!$f$2:$f$99999=$b42&$c42),longstring!$g $2:$g$99999)

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:

=========
ps. I'd open the sending workbook before I created the formula. When I have it
correct and close that workbook excel will include the path.

pps. Open the file via the UNC path, too.

Bruce wrote:

To ensure that linkages in my spreadsheet will work for other users on our
shared folders, I have used the full pathname, or UNC name, in my formulas.
It works fine for VLOOKUP, but for some reason I get the #VALUE! error when
using SUMIF in Excel 2007. I'm not sure what I'm doing incorrectly, or
simply that using the UNC name will not work in SUMIF.

Here's a sample of the syntax:

SUMIF('\\hyperdisk\data\accounting and finance\budgets\2008 CFO
Report\[PriorActual.xlsm]January'!$F$2:$F$99999,$B42&$C42,'\\hyperdisk\data \accounting
and finance\budgets\2008 CFO Report\[PriorActual.xlsm]January'!$G$2:$G$99999)

--

Dave Peterson

--

Dave Peterson

You're welcome!

The double negative (--) looks kind of funny

Yeah, it does and it takes a little time to get used to but it's more
efficient (and more robust in certain situations) than using the
multiplication form *.

--
Biff
Microsoft Excel MVP


"Bruce" wrote in message
...
Thanks for your help, both Biff and Dave!

The SUMIF only worked when I opened the source file by using the whole
path
before building the SUMIF formula, as you have suggested. This is the
less
desirable solution, since in order to edit a formula, one would always
have
to remember to open the source file, and the full path is not displayed in
the formula box when I hit the check mark while that file is open. But I
had
to explore this option since the users here are familiar with SUMIF and
not
SUMPRODUCT.

I tried the SUMPRODUCT formula which was much better. The double negative
(--) looks kind of funny and would be hard to explain to my users, so I
used
the multiply (*) operator instead and got the same results. Both
McGimpsey
and Phillips were very helpful in explaining how this all works.
Phillips
especially gave a few different ways you could go at this.

"Dave Peterson" wrote:

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

(missed the URL)

Dave Peterson wrote:

It's not the UNC path that's the problem. The problem is that =sumif()
won't
work when the sending file is closed.

But there are alternatives.

One of them is =sumproduct()

=sumproduct(--(longstring!$f$2:$f$99999=$b42&$c42),longstring!$g $2:$g$99999)

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:

=========
ps. I'd open the sending workbook before I created the formula. When
I have it
correct and close that workbook excel will include the path.

pps. Open the file via the UNC path, too.

Bruce wrote:

To ensure that linkages in my spreadsheet will work for other users
on our
shared folders, I have used the full pathname, or UNC name, in my
formulas.
It works fine for VLOOKUP, but for some reason I get the #VALUE!
error when
using SUMIF in Excel 2007. I'm not sure what I'm doing incorrectly,
or
simply that using the UNC name will not work in SUMIF.

Here's a sample of the syntax:

SUMIF('\\hyperdisk\data\accounting and finance\budgets\2008 CFO
Report\[PriorActual.xlsm]January'!$F$2:$F$99999,$B42&$C42,'\\hyperdisk\data \accounting
and finance\budgets\2008 CFO
Report\[PriorActual.xlsm]January'!$G$2:$G$99999)

--

Dave Peterson

--

Dave Peterson