SumIf - Linked Workbook - #VALUE

[Lynn]

I am using the following function in Master.xls

=SUMIF(Source.xls!Colours,A1,Source.xls!Values)

As long as both workbooks are open, it calculates properly.

When I close both workbooks, reopen Master.xls & choose to update linked
formulas, the #VALUE error appears. I tried replacing named ranges with cell
references - same error.

As soon as I open Source.xls, the formula calculates properly.

Why is #VALUE appearing & how can I open only Master.xls & have the formuala
display the calculation?

I also have =SUM(Source.xls!Values) in Master.xls which does calculate
correctly when Source.xls is closed.

HELP!

 

[Harlan Grove]

Lynn wrote...

I am using the following function in Master.xls

=SUMIF(Source.xls!Colours,A1,Source.xls!Values)

As long as both workbooks are open, it calculates properly.

When I close both workbooks, reopen Master.xls & choose to update linked
formulas, the #VALUE error appears. I tried replacing named ranges with cell
references - same error.

As soon as I open Source.xls, the formula calculates properly.

Why is #VALUE appearing & how can I open only Master.xls & have the formuala
display the calculation?

....

Excel returns references to blocks of cells in closed workbooks as
arrays. They're not, strictly speaking, ranges in the way Excel works
internally with ranges. SUMIF only accepts true range references in its
first and third arguments. When the other workbook is open, references
to block of cells in it are returned as ranges. That's not the case
when it's closed. That's the cause of your problem.

In short, use SUMIF and COUNTIF only with references to ranges in the
same workbook. If you need conditional summing or counting in other
workbooks, use SUMPRODUCT, e.g.,

=SUMPRODUCT(--(Source.xls!Colours=A1),Source.xls!Values)

(assuming A1 contains an equality criterion without wildcards).