Hi All

Currently go a problem with the SumIf Worksheet Function.

I have a list of data stored in a workbook. Lets say C:\Temp\Source.xls.

I have a Input Sheet stored in C:\Temp\Input.xls.

In cell A2 on the Input Sheet I have a value entered called March.
In cell A3 on the Input Sheet I have a formula: #Value!

It does not come up if both workbooks are open.

Kind Regards

D

=SUMIF('C:\Temp\[Source.xls]Sheet1'!$A$2:$A$30,B3,'C:\Temp\[Source.xls]Sheet1'!$B$2:$B$30)

Why does the cell come up with error

D
Both workbooks have to be open for that function to work.
Ken


On Jun 13, 6:30 am, DMc2004 wrote:
Hi All

Currently go a problem with the SumIf Worksheet Function.

I have a list of data stored in a workbook. Lets say C:\Temp\Source.xls.

I have a Input Sheet stored in C:\Temp\Input.xls.

In cell A2 on the Input Sheet I have a value entered called March.
In cell A3 on the Input Sheet I have a formula: #Value!

It does not come up if both workbooks are open.

Kind Regards

D

=SUMIF('C:\Temp\[Source.xls]Sheet1'!$A$2:$A$30,B3,'C:\Temp\[Source.xls]Shee*t1'!$B$2:$B$30)

Why does the cell come up with error

Sumif() does *not* work on closed files.

However, you could use an *array* formula, which is a combination of SUM()
and IF() to access your closed WBs.

Try this:

=SUM(IF('C:\Temp\[Source.xls]Sheet1'!$A$2:$A$30=B3,'C:\Temp\[Source.xls]Sheet1'!$B$2:$B$30,""))

--
Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of the
regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, CSE *must* be used when
revising the formula.

This returns a blank cell if there is no match for B3.

Replace the last set of double quotes ( "" ) with zero ( 0 ), if you wish to
display that return instead.
--

HTH,

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

"DMc2004" wrote in message
...
Hi All

Currently go a problem with the SumIf Worksheet Function.

I have a list of data stored in a workbook. Lets say C:\Temp\Source.xls.

I have a Input Sheet stored in C:\Temp\Input.xls.

In cell A2 on the Input Sheet I have a value entered called March.
In cell A3 on the Input Sheet I have a formula: #Value!

It does not come up if both workbooks are open.

Kind Regards

D

=SUMIF('C:\Temp\[Source.xls]Sheet1'!$A$2:$A$30,B3,'C:\Temp\[Source.xls]Sheet1'!$B$2:$B$30)

Why does the cell come up with error