Will SUMIF --ever-- work with closed workbook references?

J

John

Some of my co-workers believe beyond a reasonable doubt that they have
seen formulas in their workbooks using a SUMIF work on a closed
workbook, ie no #values. Is this ever possible?

The only conclusion I can make is that the file's workbook option of
saving external reference values was enabled, and the file was saved
when all the related workbooks were open and values were actually in
the cells. Is my hypothesis correct? Or, is SUMIF not so black and
white?

Will any function (SUMIF, COUNTIF, OFFSET, etc.) requiring a range
EVER work on a closed workbook, which creates an array type reference.
Is there a list of functions that will only work with ranges, such as
SUMIF?

TIA
 
P

Peo Sjoblom

No it won't ever work but you can use sumproduct which will work..

=SUMIF(A1:A10,"X",B1:B10)

=SUMPRODUCT(--(A1:A10="X"),B1:B10)

both formulas above will return the same result,
however the latter will work on a closed workbook while the former
will return a value error
 
H

Harlan Grove

try
=SUMIF('C:\yourfolder\[yourfile.xls]yourworksheet'!$C$6:$C$8,"<"&10)

Gosh, did you try this?!

They'd be correct in a very limited sense. If they enter such a formula with the
referenced workbook open, then close that other workbook, the formula result
won't change even if recalculation is set to Automatic. However, all this means
is that closing a workbook doesn't trigger minimal recalculation (which is all
automatic calculation does). If they were to press the [Ctrl]+[Alt]+[F9] key
combination to force a full recalc, they'd get #VALUE! errors.

No. Ranges per se only exist in *OPEN* workbooks. There are workarounds for most
situations, however. If you may need to reference closed workbooks in
conditional sums, use

=SUMPRODUCT(--(CriteriaExpression),StuffToBeSummed)

Not that I'm aware of, but individual worksheet function topics in online help
are clear when they specifically require range arguments (other than the RANK
function, which requires a Range reference as its 2nd argument - Microsoft
doesn't seem to be willing to part with the 0.000001% of its cash assets needed
to correct the help file topic for RANK in all supported languages, something
that would have been accomplished years ago in open source software which
Microsoft claims can't match their professional standards -- HOPE & PRAY THAT
OSS NEVER SINKS SO LOW AT TO APPROACH MICROSOFT'S STANDARDS).
 
J

John

Don, so you are saying that a SUMIF will properly function on a closed
workbook when the SUMIF's range argument contains the path and file
name (c:\filehere.xls...)? I thought excel would automatically display
the path in a SUMIF when/if the referenced workbook is closed.
 
D

Don Guillett

I thought I did but I guess when I closed the source wb it didn't
recalculate.
SUMPRODUCT!!!

--
Don Guillett
SalesAid Software
(e-mail address removed)
Harlan Grove said:
try
=SUMIF('C:\yourfolder\[yourfile.xls]yourworksheet'!$C$6:$C$8,"<"&10)

Gosh, did you try this?!

They'd be correct in a very limited sense. If they enter such a formula with the
referenced workbook open, then close that other workbook, the formula result
won't change even if recalculation is set to Automatic. However, all this means
is that closing a workbook doesn't trigger minimal recalculation (which is all
automatic calculation does). If they were to press the [Ctrl]+[Alt]+[F9] key
combination to force a full recalc, they'd get #VALUE! errors.

No. Ranges per se only exist in *OPEN* workbooks. There are workarounds for most
situations, however. If you may need to reference closed workbooks in
conditional sums, use

=SUMPRODUCT(--(CriteriaExpression),StuffToBeSummed)

Not that I'm aware of, but individual worksheet function topics in online help
are clear when they specifically require range arguments (other than the RANK
function, which requires a Range reference as its 2nd argument - Microsoft
doesn't seem to be willing to part with the 0.000001% of its cash assets needed
to correct the help file topic for RANK in all supported languages, something
that would have been accomplished years ago in open source software which
Microsoft claims can't match their professional standards -- HOPE & PRAY THAT
OSS NEVER SINKS SO LOW AT TO APPROACH MICROSOFT'S STANDARDS).
 
R

RagDyeR

What's the chances that your co-workers saw *THIS* type of formula:?

=SUM(IF(C:\full_path!A1:A100>50,C:\full_path!A1:A100,"Not_There"))

As an array formula, this *will* work on closed WBs, *when* entered with CSE
(<Ctrl> <Shift> <Enter>).

Did they notice whether or not the formula was enclosed in curly brackets?
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

Don, so you are saying that a SUMIF will properly function on a closed
workbook when the SUMIF's range argument contains the path and file
name (c:\filehere.xls...)? I thought excel would automatically display
the path in a SUMIF when/if the referenced workbook is closed.
 
J

John

What's the chances that your co-workers saw *THIS* type of formula:?
=SUM(IF(C:\full_path!A1:A100>50,C:\full_path!A1:A100,"Not_There"))


No one would've known to use a SUM with a nested IF...I think they
just get confused when they open a workbook and see values, but then
"mysteriously" see errors when an recalc command is given. They blame
it on the "network."
 
Joined
Dec 14, 2011
Messages
1
Reaction score
0
so SUM works with closed workbooks but SUMIFS does not work on closed workbooks? I am puzzled because if you review the function using the function reviewer you will see it is able to solve the function although it doesn't provide a result it does sees all the items listed in the column it is referencing.

wMR+LRsCelSHwAAAABJRU5ErkJggg==

k12VdBuK14kAAAAASUVORK5CYII=
 
Last edited:

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top