Posted to microsoft.public.excel.misc
|
|
Sumif
Thanks for the feed-back.
--
Regards,
RD
---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Pritesh" wrote in message
...
Hi RD,
I tried below suggested formula, which worked-out to discard "#Value"
error.
Thanx.
"RagDyeR" wrote:
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!
================================================== ===
"Pritesh" wrote in message
...
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.
"Muhammed Rafeek M" wrote:
Hi
Better use pivot table. If you are not clear, send me mail to
with your problem files, i can guide you. I am also
from
India.
"Pritesh" wrote:
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.
|