| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF and #VALUE! when source is closed?
Hi all,
I have stuck one more problem. I tried to search through earlier questions but didn't find solution. I have 2 files, first has product code (column A), name and another information between and batch amount in packaging column J and kg column K. In second file there is column A the same product code (6 numbers) and column G is total amount per year, H is total amount kg per year. I have formula =COUNTIF('[File1.xls]Sheet1'!$A$3:$A$316;A2) and it works fine if source is open. After source is closed or only file2 is opened and updated, error #VALUE! appears. Is this avoidable and if so, how...? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMIF and #VALUE! when source is closed?
I have both formulas, SUMIF and COUNTIF doing this error in columns G and H
and both do this same thing. "NeedToKnow" kirjoitti: Hi all, I have stuck one more problem. I tried to search through earlier questions but didn't find solution. I have 2 files, first has product code (column A), name and another information between and batch amount in packaging column J and kg column K. In second file there is column A the same product code (6 numbers) and column G is total amount per year, H is total amount kg per year. I have formula =COUNTIF('[File1.xls]Sheet1'!$A$3:$A$316;A2) and it works fine if source is open. After source is closed or only file2 is opened and updated, error #VALUE! appears. Is this avoidable and if so, how...? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMIF and #VALUE! when source is closed?
On 14 Feb, 08:53, NeedToKnow
wrote: I have both formulas, SUMIF and COUNTIF doing this error in columns G and H and both do this same thing. "NeedToKnow" kirjoitti: Hi all, I have stuck one more problem. I tried to search through earlier questions but didn't find solution. I have 2 files, first has product code (column A), name and another information between and batch amount in packaging column J and kg column K. In second file there is column A the same product code (6 numbers) and column G is total amount per year, H is total amount kg per year. I have formula =COUNTIF('[File1.xls]Sheet1'!$A$3:$A$316;A2) and it works fine if source is open. After source is closed or only file2 is opened and updated, error #VALUE! appears. Is this avoidable and if so, how...? Hi. SUMIF/COUNTIF don't work with closed workbooks. You'll need to try SUMPRODUCT. Try the link below for instructions:- http://www.xldynamic.com/source/xld.SUMPRODUCT.html Regards, Matt Richardson http://teachr.blogspot.com |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMIF and #VALUE! when source is closed?
Ok, thank you. So basicly I have done all ok but using the wrong function.
Happy Valentine's day! "Matt Richardson" kirjoitti: On 14 Feb, 08:53, NeedToKnow wrote: I have both formulas, SUMIF and COUNTIF doing this error in columns G and H and both do this same thing. "NeedToKnow" kirjoitti: Hi all, I have stuck one more problem. I tried to search through earlier questions but didn't find solution. I have 2 files, first has product code (column A), name and another information between and batch amount in packaging column J and kg column K. In second file there is column A the same product code (6 numbers) and column G is total amount per year, H is total amount kg per year. I have formula =COUNTIF('[File1.xls]Sheet1'!$A$3:$A$316;A2) and it works fine if source is open. After source is closed or only file2 is opened and updated, error #VALUE! appears. Is this avoidable and if so, how...? Hi. SUMIF/COUNTIF don't work with closed workbooks. You'll need to try SUMPRODUCT. Try the link below for instructions:- http://www.xldynamic.com/source/xld.SUMPRODUCT.html Regards, Matt Richardson http://teachr.blogspot.com |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMIF and #VALUE! when source is closed?
It was only the "wrong" function because you are working across 2 workbooks
rather than within just one. I wouldn't beat myself up too badly over that! "NeedToKnow" wrote: Ok, thank you. So basicly I have done all ok but using the wrong function. Happy Valentine's day! "Matt Richardson" kirjoitti: On 14 Feb, 08:53, NeedToKnow wrote: I have both formulas, SUMIF and COUNTIF doing this error in columns G and H and both do this same thing. "NeedToKnow" kirjoitti: Hi all, I have stuck one more problem. I tried to search through earlier questions but didn't find solution. I have 2 files, first has product code (column A), name and another information between and batch amount in packaging column J and kg column K. In second file there is column A the same product code (6 numbers) and column G is total amount per year, H is total amount kg per year. I have formula =COUNTIF('[File1.xls]Sheet1'!$A$3:$A$316;A2) and it works fine if source is open. After source is closed or only file2 is opened and updated, error #VALUE! appears. Is this avoidable and if so, how...? Hi. SUMIF/COUNTIF don't work with closed workbooks. You'll need to try SUMPRODUCT. Try the link below for instructions:- http://www.xldynamic.com/source/xld.SUMPRODUCT.html Regards, Matt Richardson http://teachr.blogspot.com |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Updating Links from Closed Source Workbooks | Excel Discussion (Misc queries) | |||
| How to update destination file with source files closed? | Excel Discussion (Misc queries) | |||
| SUMIF Returns a #VALUE error when external source is closed | Excel Worksheet Functions | |||
| SUMIF Returns a #VALUE error when external source is closed | Excel Discussion (Misc queries) | |||
| SUMIF Returns a #VALUE error when external source is closed | Excel Worksheet Functions | |||
Hybrid Mode
