Also note that SUMPRODUCT cannot use the whole range like

A:A

whereas SUMIF can

in needs to be specified like

A1:A10000

as an example

otherwise you will also get a num error



--
Regards,

Peo Sjoblom


"Peo Sjoblom" wrote in message
...
Since you are using named ranges I expect that they are not of the same
size, that would explain the num errors, in SUMPRODUCT the ranges need to
be of the same size so I suspect that TB and TBTL are of a different size.

It should otherwise work, I have used it many times without any problems


--
Regards,

Peo Sjoblom

"Margie" wrote in message
...
I've tried it several ways and can't seem to be able to use this
SUMPRODUCT -
either getting wrong numbers or #NUM error. my orig formula looks at a
table
and finds a value then only would add up if it found that value on the
second
table the $"s in the total column. It seems to be adding up the entire
column or giving me the error. suggestions?

"Peo Sjoblom" wrote:

Yes it does but sumproduct can do what you want, post the formula you
have
when the source files are open, since Excel will put the path when it is
closed it is easier (less to type etc. for us to help you

For instance


=SUMIF(A2:A1000,C2,B2:B1000)

will sum B when A equals the value in C2

the equivalent using SUMPRODUCT would look like


=SUMPRODUCT(--(A2:A1000=C2),B2:B1000)

and it will work when the source book is closed, the drawback is that it
is
slower than SUMIF
since it is a de facto array formula albeit not entered as one


--
Regards,

Peo Sjoblom



"Margie" wrote in message
...
do you know if excel 2007 still requires that the source file be open?

"Margie" wrote:

thanks for the information; doesn't make me happy, but it explains it
anyway.

"bj" wrote:

Sumif needs to have the external file open, as does countif and
several
other
functions. I do not have a list of all of the which need to have
the
other
file open.

"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)