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
 
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.
 
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.
 
Top