Dave,

Your solution worked great. I see my mistake now. Thanks so much for your
help.

"Dave Peterson" wrote:

I don't see why your formula requires the sending workbook to be open. Are you
sure that Requisitions.xls is really a normal workbook? Could it be a CSV file
just renamed to .xls? (It kind of looks that way based on the name of the
workbook and worksheet.)

If it's a normal workbook, then your formula is normally written like:

=SUMPRODUCT(--([Requisitions.xls]Requisitions!$H$6:$H$6553=A2),
--(MID([Requisitions.xls]Requisitions!$G$6:$G$6553,7,5)="80801"),
[Requisitions.xls]Requisitions!$F$6:$F$6553)





Vince wrote:

The formula below only updates the cell if the worksheet "Requisitions.xls"
is open. Otherwise, I get the message to update the link when I open the
current worksheet but all that is displayed is #REF in each cell that the
formula exists. Is there a way around this? I would like not to have to
open the other workbook in order for the cells to update.

I am summing all the values in Requisitions.xls column F where column H =
the value in A2 and column G = 80801. Any help or suggestions you have are
appreciated.

=SUMPRODUCT(--([Requisitions.xls]Requisitions!$H$6:[Requisitions.xls]Requisitions!$H$6553=A2),--(MID([Requisitions.xls]Requisitions!$G$6:[Requisitions.xls]Requisitions!$G$6553,7,5)="80801"),[Requisitions.xls]Requisitions!$F$6:[Requisitions.xls]Requisitions!$F$6553)

--

Dave Peterson