Sumif

[Pritesh]

Hi, this is Pritesh from India. I work for a Pharma & Well Being Group and
prepare monthly MIS reports in Excel. I have to link expense-codes and
relevant amounts to my file from a source file (both .xls files), for which I
use Sumif formula
(=SUMIF('C:\[Source.xls]Jun06'!$F:$F,ExpCode,'C:\[Source.xls]Jun06'!$I:$I).
However, the problem is, unless I keep my source data file open, alongwith
main file, the formula returns with "#Value" error. Can someone help me
discard "#Value" error.

 

[Muhammed Rafeek M]

Hi
Better use pivot table. If you are not clear, send me mail to
(e-mail address removed) with your problem files, i can guide you. I am also from
India.

 

[Pritesh]

Hi Muhammed,

Thanx for your concern & response, but, you seem to be addictive to Pivot
Table, as, for both of my problems, you recommended me to use it. Even I use
Pivot Table, but, only when and where required.

Hi
Better use pivot table. If you are not clear, send me mail to
(e-mail address removed) with your problem files, i can guide you. I am also from
India.

Hi, this is Pritesh from India. I work for a Pharma & Well Being Group and
prepare monthly MIS reports in Excel. I have to link expense-codes and
relevant amounts to my file from a source file (both .xls files), for which I
use Sumif formula
(=SUMIF('C:\[Source.xls]Jun06'!$F:$F,ExpCode,'C:\[Source.xls]Jun06'!$I:$I).
However, the problem is, unless I keep my source data file open, alongwith
main file, the formula returns with "#Value" error. Can someone help me
discard "#Value" error.

 

[Muhammed Rafeek M]

pls send me your work file to me: (e-mail address removed)

you can use: =GETPIVOTDATA(........) function

Hi Muhammed,

Thanx for your concern & response, but, you seem to be addictive to Pivot
Table, as, for both of my problems, you recommended me to use it. Even I use
Pivot Table, but, only when and where required.

Hi
Better use pivot table. If you are not clear, send me mail to
(e-mail address removed) with your problem files, i can guide you. I am also from
India.

Hi, this is Pritesh from India. I work for a Pharma & Well Being Group and
prepare monthly MIS reports in Excel. I have to link expense-codes and
relevant amounts to my file from a source file (both .xls files), for which I
use Sumif formula
(=SUMIF('C:\[Source.xls]Jun06'!$F:$F,ExpCode,'C:\[Source.xls]Jun06'!$I:$I).
However, the problem is, unless I keep my source data file open, alongwith
main file, the formula returns with "#Value" error. Can someone help me
discard "#Value" error.

 

[RagDyeR]

Sumif is among the functions that don't work on closed WBs, as is Indirect
and Countif and others.

One work-around is this *array* formula using the Sum() and IF()
combination:

=SUM(IF('C:\[Source.xls]Jun06'!$F1:$F1000=ExpCode,'C:\[Source.xls]Jun06'!$I1
:$I1000))

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

Also NOTE:
Array formulas *cannot* use entire column references ( F:F, I:I ).

--

HTH,

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

Hi Muhammed,

Thanx for your concern & response, but, you seem to be addictive to Pivot
Table, as, for both of my problems, you recommended me to use it. Even I use
Pivot Table, but, only when and where required.

Hi
Better use pivot table. If you are not clear, send me mail to
(e-mail address removed) with your problem files, i can guide you. I am also from
India.

Hi, this is Pritesh from India. I work for a Pharma & Well Being Group and
prepare monthly MIS reports in Excel. I have to link expense-codes and
relevant amounts to my file from a source file (both .xls files), for which I
use Sumif formula
(=SUMIF('C:\[Source.xls]Jun06'!$F:$F,ExpCode,'C:\[Source.xls]Jun06'!$I:$I).
However, the problem is, unless I keep my source data file open, alongwith
main file, the formula returns with "#Value" error. Can someone help me
discard "#Value" error.

 

[Pritesh]

Hi RD,

I tried below suggested formula, which worked-out to discard "#Value" error.
Thanx.

Sumif is among the functions that don't work on closed WBs, as is Indirect
and Countif and others.

One work-around is this *array* formula using the Sum() and IF()
combination:

=SUM(IF('C:\[Source.xls]Jun06'!$F1:$F1000=ExpCode,'C:\[Source.xls]Jun06'!$I1
:$I1000))

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

Also NOTE:
Array formulas *cannot* use entire column references ( F:F, I:I ).

--

HTH,

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

Hi Muhammed,

Thanx for your concern & response, but, you seem to be addictive to Pivot
Table, as, for both of my problems, you recommended me to use it. Even I use
Pivot Table, but, only when and where required.

Hi
Better use pivot table. If you are not clear, send me mail to
(e-mail address removed) with your problem files, i can guide you. I am also from
India.

Hi, this is Pritesh from India. I work for a Pharma & Well Being Group and
prepare monthly MIS reports in Excel. I have to link expense-codes and
relevant amounts to my file from a source file (both .xls files), for which I
use Sumif formula
(=SUMIF('C:\[Source.xls]Jun06'!$F:$F,ExpCode,'C:\[Source.xls]Jun06'!$I:$I).
However, the problem is, unless I keep my source data file open, alongwith
main file, the formula returns with "#Value" error. Can someone help me
discard "#Value" error.

 

[Ragdyer]

Thanks for the feed-back.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

Hi RD,

I tried below suggested formula, which worked-out to discard "#Value"
error.
Thanx.

Sumif is among the functions that don't work on closed WBs, as is
Indirect
and Countif and others.

One work-around is this *array* formula using the Sum() and IF()
combination:

=SUM(IF('C:\[Source.xls]Jun06'!$F1:$F1000=ExpCode,'C:\[Source.xls]Jun06'!$I1
:$I1000))

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

Also NOTE:
Array formulas *cannot* use entire column references ( F:F, I:I ).

--

HTH,

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

Hi Muhammed,

Thanx for your concern & response, but, you seem to be addictive to Pivot
Table, as, for both of my problems, you recommended me to use it. Even I
use
Pivot Table, but, only when and where required.

Hi
Better use pivot table. If you are not clear, send me mail to
(e-mail address removed) with your problem files, i can guide you. I am also from
India.

:

Hi, this is Pritesh from India. I work for a Pharma & Well Being
Group and
prepare monthly MIS reports in Excel. I have to link expense-codes
and
relevant amounts to my file from a source file (both .xls files), for which I
use Sumif formula
(=SUMIF('C:\[Source.xls]Jun06'!$F:$F,ExpCode,'C:\[Source.xls]Jun06'!$I:$I).
However, the problem is, unless I keep my source data file open, alongwith
main file, the formula returns with "#Value" error. Can someone help
me
discard "#Value" error.