SUMIF and #VALUE! when source is closed?

N

NeedToKnow

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...?
 
N

NeedToKnow

I have both formulas, SUMIF and COUNTIF doing this error in columns G and H
and both do this same thing.

"NeedToKnow" kirjoitti:
 
M

Matt Richardson

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
 
N

NeedToKnow

Ok, thank you. So basicly I have done all ok but using the wrong function.

Happy Valentine's day!


"Matt Richardson" kirjoitti:
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
 
J

JLatham

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 said:
Ok, thank you. So basicly I have done all ok but using the wrong function.

Happy Valentine's day!


"Matt Richardson" kirjoitti:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top