Hi,
I tried both formulas and I'm getting a #REF error with both. Any ideas?

"Peo Sjoblom" wrote:

=SUMPRODUCT(('[film1998-2003a.xls]2003'!$C$2:$C$8=H2)*('[film1998-2003a.xls
]2003'!$E$2:$E$8<=H2)*('[film1998-2003a.xls]2003'!$F$2:$F$8))

or the simplified

=SUMPRODUCT(--('[film1998-2003a.xls]2003'!$C$2:$C$8=H2),--('[film1998-2003a
..xls]2003'!$E$2:$E$8<=H2),'[film1998-2003a.xls]2003'!$F$2:$F$8)


I also noted that in your sheet2 example you are using 2 different cells as
criteria (C2 and E2) while this one uses H2


--

Regards,

Peo Sjoblom

"cottage6" wrote in message
...
Hi everyone,
I have 2 spreadsheets, Spreadsheet 1 has a beginning batch number in Col.
C,
an ending batch number in Col. E, and a tape number in Col. F. The other
spreadsheet contains batch numbers in Col. H. I need to get the tape
number
from Spreadsheet #1 that corresponds with the batch number in Spreadsheet
#2;
the batch number can fall anywhere within the range of beginning and
ending
batch numbers. I just started to realize the value in using SUMPRODUCT
and
got the correct tape number using that function when I tried it on
Spreadsheet #2:
{=SUMPRODUCT((C2:C8=C2)*(E2:E8<=E2)*F2:F8)}

However, when I tried to create the formula on Spreadsheet #1 and have it
refer back to the other spreadsheet I ended up with what I'll probably get
for Christmas from the CIO. Obviously I've missed something major here;
can
anyone help me?

{=SUMPRODUCT(('[film1998-2003a.xls]2003'!$C$2:$C$8=H2*('[film1998-2003a.xls
]2003'!$E$2:$E$8<=H2)*'[film1998-2003a.xls]2003'!$F$2:$F$8))}