Now I'm getting a #VALUE error instead so I probably did enter it wrong
before. Is the problem that the beginning and ending batch numbers and tape
number are in a different file?

"Peo Sjoblom" wrote:

It's your formula, I just moved some parenthesis so I have no clues why you
would get a ref error if your formula returns zero?

You must have not applied it correctly, it might be easier if you edit your
formula

put ) after the first H2 in your formula, put ( before the last range, i.e.
change

*'[film1998-2003a.xls]2003'!$F$2:$F$8))

to

*('[film1998-2003a.xls]2003'!$F$2:$F$8))


--

Regards,

Peo Sjoblom

"cottage6" wrote in message
...
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))}