#VALUE! error when using UNC path name in SUMIF

[Bruce]

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)

 

[T. Valko]

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)

 

[Dave Peterson]

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 here:
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.

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)

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 here:
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.

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)

 

[Bruce]

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.

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

(missed the URL)

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 here:
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.

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)

 

[T. Valko]

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

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.

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

(missed the URL)

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 here:
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)