Posted to microsoft.public.excel.misc
|
|
#VALUE! error when using UNC path name in SUMIF
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
|