SUMIF Value error

[Margie]

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)

 

[bj]

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.

 

[Dave Peterson]

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]

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

 

[Margie]

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.

 

[Margie]

do you know if excel 2007 still requires that the source file be open?

 

[Peo Sjoblom]

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

 

[Margie]

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]

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

 

[Peo Sjoblom]

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

 

[Margie]

Yes they are so i'm sure that is my problem. TB is the entire database so it
can look for the product i want in column A while TBTL is the total column
only so it would add up the product lines if found in A with the totals in L
only. The columns B-K also have $ values and the file comes prepared to me
by our IS dept so i don't have flexibility in what is in the files. i did
know i would have to redo the ranges so i do have TB & TBTL changes from L:L
to L1:L2000 etc. i'm thinking i've hit another roadblock on this one, but it
seems like i should be able to work around it. problem is the product i want
it to find is on the active workbook on another sheet then it has to find
those applicable lines in the workbook i don't want to have to keep open.

 

[Dave Peterson]

This technique will work in xl2003. But you can't use whole columns.

I don't understand what you mean about it giving you the entire file. This
=sum(if()), =sumif(), and =sumproduct() will add all the values that meet the
criteria--not the entire file and not just one line.

 

[bj]

one "not friendly" way to handle closed books generated by others is to have
a sheet which duplicates the info in your book
goto the source book
select all in the sheet of interest
go to your book insert a worksheet selct cell A1 and paste special link
you can then do all of the claulations you want on your book alone. If it
is ssensitive information which you don't wnat people to look at
further select all on your copy sheet and format cells number custom ;;; and
things on the sheet will not be seen

as always it will keep most honest people honest but is not secure against a
dedicated "Player"