I tried that, but it gives me the entire files total (4.4million instead of
the line that it is suppose to look for a match on of only 10k). I only have
excel 2003 so i guess this won't work for me. I was also told that certain
functions require you to have the source file open and SUMIF and COUNTIF are
some of them that require this. does 2007 require that as well? Of course
that doesn't solve my problem since opening the source file each time this
file is used is too much add'l work for the users.

"Dave Peterson" wrote:

You could convert to an array formula:

=sum(if(....))
This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can only use the whole column in xl2007.

Or you could use =sumproduct()

=sumproduct(--(somerange=somevalue))

You can't use the whole column with this, too (until xl2007).

Margie wrote:

getting #Value error in cell if source file isn't open when using SUMIF
formula. Once file is opened the correct $$/amounts appear and there are no
errors. why am i getting value errors using this formula??? see example of
formula i am questioning below.

=SUMIF('R:\Signature\ATB\atb seq 23
Jun07.xls'!TB,Payors!C17,'R:\Signature\ATB\atb seq 23
Jun07.xls'!TBTL)+SUMIF('R:\Signature\ATB\atb seq 23
Jun07.xls'!TB,Payors!C23,'R:\Signature\ATB\atb seq 23 Jun07.xls'!TBTL)

--

Dave Peterson