Can I use SUMPRODUCT for this?

C

cottage6

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

Peo Sjoblom

=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 said:
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?
Click to expand...
{=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))}
 
C

cottage6

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

Peo Sjoblom said:
=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 said:
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?
Click to expand...
{=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))}
Click to expand...
 
P

Peo Sjoblom

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 said:
Hi,
I tried both formulas and I'm getting a #REF error with both. Any ideas?

Peo Sjoblom said:
=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 said:
Hi everyone,
I have 2 spreadsheets, Spreadsheet 1 has a beginning batch number in
Click to expand...
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
Click to expand...
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
Click to expand...
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
Click to expand...
here;
can
anyone help me?
Click to expand...
{=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))}
Click to expand...
Click to expand...
 
C

cottage6

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 said:
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 said:
Hi,
I tried both formulas and I'm getting a #REF error with both. Any ideas?

Peo Sjoblom said:
=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

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))}
Click to expand...
Click to expand...
Click to expand...
 
P

Peo Sjoblom

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 said:
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 said:
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 said:
Hi,
I tried both formulas and I'm getting a #REF error with both. Any ideas?

:
Click to expand...
=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
Click to expand...
=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
Click to expand...
cells
as
criteria (C2 and E2) while this one uses H2


--

Regards,

Peo Sjoblom

Hi everyone,
I have 2 spreadsheets, Spreadsheet 1 has a beginning batch number
Click to expand...
in
Col.
C,
an ending batch number in Col. E, and a tape number in Col. F.
Click to expand...
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
Click to expand...
have
it
refer back to the other spreadsheet I ended up with what I'll
Click to expand...
probably
get
for Christmas from the CIO. Obviously I've missed something major here;
can
anyone help me?
Click to expand...
{=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))}
Click to expand...
Click to expand...
Click to expand...
 
Top