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

=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))}

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

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

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

VALUE errors mean that you are trying to calculate with text, do you have
any text values in any of the columns? If so you would need another approach
using index and a double match


--

Regards,

Peo Sjoblom

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