SUMIF and #VALUE! when source is closed?

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

 

[NeedToKnow]

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

"NeedToKnow" kirjoitti:

 

[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

 

[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

 

[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!

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