 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / June 2007SUMIF Multiple Workbooks
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 > Hi All > [quoted text clipped - 16 lines] > > 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,""))  SignatureArray 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! ===================================================== 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 |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.