My sumif formula only works if I have the source document open. If I
open the file with the sumif first and update links it returns #VALUE!
When I open the source document it works automatically. I know the
reason is because I'm referencing another workbook. Is there something
I need to add to the formula or is there an option in excel I need to
adjust?


--
Scorpvin
------------------------------------------------------------------------
Scorpvin's Profile: http://www.excelforum.com/member.php...o&userid=27678
View this thread: http://www.excelforum.com/showthread...hreadid=471902

Not sure how to make sumif work, but Sumproduct may be an alternative.

As an example, this formula will return the number of times "jeff" appears
in range A1:A4 of Book1.

=SUMPRODUCT(--('H:\Blakley\Excel\[Book1.xls]Sheet1'!A1:A4="jeff"))

"Scorpvin" wrote:


My sumif formula only works if I have the source document open. If I
open the file with the sumif first and update links it returns #VALUE!
When I open the source document it works automatically. I know the
reason is because I'm referencing another workbook. Is there something
I need to add to the formula or is there an option in excel I need to
adjust?


--
Scorpvin
------------------------------------------------------------------------
Scorpvin's Profile: http://www.excelforum.com/member.php...o&userid=27678
View this thread: http://www.excelforum.com/showthread...hreadid=471902

You can use a combination of SUM and IF in an array formula to return your
values from closed WBs.

Post your formula for help.
--
Regards,

RD

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

"Scorpvin" wrote in
message ...

My sumif formula only works if I have the source document open. If I
open the file with the sumif first and update links it returns #VALUE!
When I open the source document it works automatically. I know the
reason is because I'm referencing another workbook. Is there something
I need to add to the formula or is there an option in excel I need to
adjust?


--
Scorpvin
------------------------------------------------------------------------
Scorpvin's Profile:
http://www.excelforum.com/member.php...o&userid=27678
View this thread: http://www.excelforum.com/showthread...hreadid=471902

RD,
Here is the formula. Note the source is an different workbook.

=SUMIF('C:\Documents and Settings\Hotspots\My Documents\[Source
IFSUM.xls]Sheet1'!$A:$B,"Rectangle",'C:\Documents and
Settings\Hotspots\My Documents\[Source IFSUM.xls]Sheet1'!$B:$B)


--
Scorpvin
------------------------------------------------------------------------
Scorpvin's Profile: http://www.excelforum.com/member.php...o&userid=27678
View this thread: http://www.excelforum.com/showthread...hreadid=471902

Why are you searching 2 columns (A:B) for "Rectangle",
and returning the values from a single Column B?
Typo?

Anyway, here's the simple syntax to follow in this *array* formula:

=SUM(IF(A1:A1000="Rectangle",B1:B1000))

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, notice that you cannot use entire Column references (A:A) in array
formulas.

You should be able to in the next version of XL though.
--
HTH,

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


"Scorpvin" wrote in
message ...

RD,
Here is the formula. Note the source is an different workbook.

=SUMIF('C:\Documents and Settings\Hotspots\My Documents\[Source
IFSUM.xls]Sheet1'!$A:$B,"Rectangle",'C:\Documents and
Settings\Hotspots\My Documents\[Source IFSUM.xls]Sheet1'!$B:$B)


--
Scorpvin
------------------------------------------------------------------------
Scorpvin's Profile:
http://www.excelforum.com/member.php...o&userid=27678
View this thread: http://www.excelforum.com/showthread...hreadid=471902

You are awesome! Thanks for the help.


--
Scorpvin
------------------------------------------------------------------------
Scorpvin's Profile: http://www.excelforum.com/member.php...o&userid=27678
View this thread: http://www.excelforum.com/showthread...hreadid=471902

Can how do you include a wildcard character in an array formula such as
*
Example Rectang*


--
Scorpvin
------------------------------------------------------------------------
Scorpvin's Profile: http://www.excelforum.com/member.php...o&userid=27678
View this thread: http://www.excelforum.com/showthread...hreadid=471902