Need urgent help with SUMPRODUCT()
 

Hello,

I have an Excel to track performance. Sheet1 is performance table where user
input their ID, date, part#, side, start seq & stop seq daily. Then
on a reference sheet called "Ref" with field: part#, side, seq#, time(min).

I need a formula to put in the column G in sheet1 to calculate the time
based on a match of part#, side, start seq and stop seq of "Ref" sheet.
Thanks

Example:
Sheet1 - User input data
A B C D E F G
ID date part# side start seq stop seq Calculated Time
001 1/8/08 1360-1 1st 100 400 55 (5+10+10+30)
002 1/10/08 1360-1 1st 300 500 65 (10+30+25)

Sheet2 - Reference
part# side start seq time (min)
1360-1 1st 100 5
1360-1 1st 200 10
1360-1 1st 300 10
1360-1 1st 400 30
1360-1 1st 500 25
1360-1 1st 600 60
1560-3 2nd 100 10
...........

Need urgent help with SUMPRODUCT()
 

See this screencap:

http://img528.imageshack.us/img528/2058/camul0.jpg

As you can see in the screencap the formula returns the correct results.

This is the formula entered in G2:

=SUMPRODUCT(--(A$10:A$16=C2),--(B$10:B$16=D2),--(C$10:C$16=E2),--(C$10:C$16<=F2),D$10:D$16)

There is also a fragmented post from this thread that I replied to.


--
Biff
Microsoft Excel MVP


"Cam" wrote in message
...
Hello,

I have an Excel to track performance. Sheet1 is performance table where
user
input their ID, date, part#, side, start seq & stop seq daily. Then
on a reference sheet called "Ref" with field: part#, side, seq#,
time(min).

I need a formula to put in the column G in sheet1 to calculate the time
based on a match of part#, side, start seq and stop seq of "Ref" sheet.
Thanks

Example:
Sheet1 - User input data
A B C D E F G
ID date part# side start seq stop seq Calculated
Time
001 1/8/08 1360-1 1st 100 400 55
(5+10+10+30)
002 1/10/08 1360-1 1st 300 500 65 (10+30+25)

Sheet2 - Reference
part# side start seq time (min)
1360-1 1st 100 5
1360-1 1st 200 10
1360-1 1st 300 10
1360-1 1st 400 30
1360-1 1st 500 25
1360-1 1st 600 60
1560-3 2nd 100 10
..........

Need urgent help with SUMPRODUCT()
 

Valko,

Thanks for all your help. I re-entered the data manually and it seemed to be
working. But, the actual raw data I worked with is from an import Access
database. Some calculated and some give me 0 result. Maybe, there have to be
something about the character when importing data from Access db. Is there a
way to inport the data correctly?

"T. Valko" wrote:

See this screencap:

http://img528.imageshack.us/img528/2058/camul0.jpg

As you can see in the screencap the formula returns the correct results.

This is the formula entered in G2:

=SUMPRODUCT(--(A$10:A$16=C2),--(B$10:B$16=D2),--(C$10:C$16=E2),--(C$10:C$16<=F2),D$10:D$16)

There is also a fragmented post from this thread that I replied to.


--
Biff
Microsoft Excel MVP


"Cam" wrote in message
...
Hello,

I have an Excel to track performance. Sheet1 is performance table where
user
input their ID, date, part#, side, start seq & stop seq daily. Then
on a reference sheet called "Ref" with field: part#, side, seq#,
time(min).

I need a formula to put in the column G in sheet1 to calculate the time
based on a match of part#, side, start seq and stop seq of "Ref" sheet.
Thanks

Example:
Sheet1 - User input data
A B C D E F G
ID date part# side start seq stop seq Calculated
Time
001 1/8/08 1360-1 1st 100 400 55
(5+10+10+30)
002 1/10/08 1360-1 1st 300 500 65 (10+30+25)

Sheet2 - Reference
part# side start seq time (min)
1360-1 1st 100 5
1360-1 1st 200 10
1360-1 1st 300 10
1360-1 1st 400 30
1360-1 1st 500 25
1360-1 1st 600 60
1560-3 2nd 100 10
..........

Need urgent help with SUMPRODUCT()
 

Importing data from another application can sometimes lead to importing
unseen characters like leading/trailing spaces that casue problems with
formulas.

Every time I import something into Excel I run this macro to "clean" the
data:

http://www.mvps.org/dmcritchie/excel/join.htm#trimall


--
Biff
Microsoft Excel MVP


"Cam" wrote in message
...
Valko,

Thanks for all your help. I re-entered the data manually and it seemed to
be
working. But, the actual raw data I worked with is from an import Access
database. Some calculated and some give me 0 result. Maybe, there have to
be
something about the character when importing data from Access db. Is there
a
way to inport the data correctly?

"T. Valko" wrote:

See this screencap:

http://img528.imageshack.us/img528/2058/camul0.jpg

As you can see in the screencap the formula returns the correct results.

This is the formula entered in G2:

=SUMPRODUCT(--(A$10:A$16=C2),--(B$10:B$16=D2),--(C$10:C$16=E2),--(C$10:C$16<=F2),D$10:D$16)

There is also a fragmented post from this thread that I replied to.


--
Biff
Microsoft Excel MVP


"Cam" wrote in message
...
Hello,

I have an Excel to track performance. Sheet1 is performance table where
user
input their ID, date, part#, side, start seq & stop seq daily. Then
on a reference sheet called "Ref" with field: part#, side, seq#,
time(min).

I need a formula to put in the column G in sheet1 to calculate the time
based on a match of part#, side, start seq and stop seq of "Ref" sheet.
Thanks

Example:
Sheet1 - User input data
A B C D E F
G
ID date part# side start seq stop seq
Calculated
Time
001 1/8/08 1360-1 1st 100 400 55
(5+10+10+30)
002 1/10/08 1360-1 1st 300 500 65
(10+30+25)

Sheet2 - Reference
part# side start seq time (min)
1360-1 1st 100 5
1360-1 1st 200 10
1360-1 1st 300 10
1360-1 1st 400 30
1360-1 1st 500 25
1360-1 1st 600 60
1560-3 2nd 100 10
..........

Need urgent help with SUMPRODUCT()
 

Valko,

I was wondering whether text field or numeric field from Access have
something to do with why it is not calculating or give incorrect calculation.
Just for information on my actual data from Access part# and side and start
and stop seq are text field and time are numeric fields.

Should I change the start and stop sequence to numeric field instead?

"T. Valko" wrote:

Importing data from another application can sometimes lead to importing
unseen characters like leading/trailing spaces that casue problems with
formulas.

Every time I import something into Excel I run this macro to "clean" the
data:

http://www.mvps.org/dmcritchie/excel/join.htm#trimall


--
Biff
Microsoft Excel MVP


"Cam" wrote in message
...
Valko,

Thanks for all your help. I re-entered the data manually and it seemed to
be
working. But, the actual raw data I worked with is from an import Access
database. Some calculated and some give me 0 result. Maybe, there have to
be
something about the character when importing data from Access db. Is there
a
way to inport the data correctly?

"T. Valko" wrote:

See this screencap:

http://img528.imageshack.us/img528/2058/camul0.jpg

As you can see in the screencap the formula returns the correct results.

This is the formula entered in G2:

=SUMPRODUCT(--(A$10:A$16=C2),--(B$10:B$16=D2),--(C$10:C$16=E2),--(C$10:C$16<=F2),D$10:D$16)

There is also a fragmented post from this thread that I replied to.


--
Biff
Microsoft Excel MVP


"Cam" wrote in message
...
Hello,

I have an Excel to track performance. Sheet1 is performance table where
user
input their ID, date, part#, side, start seq & stop seq daily. Then
on a reference sheet called "Ref" with field: part#, side, seq#,
time(min).

I need a formula to put in the column G in sheet1 to calculate the time
based on a match of part#, side, start seq and stop seq of "Ref" sheet.
Thanks

Example:
Sheet1 - User input data
A B C D E F
G
ID date part# side start seq stop seq
Calculated
Time
001 1/8/08 1360-1 1st 100 400 55
(5+10+10+30)
002 1/10/08 1360-1 1st 300 500 65
(10+30+25)

Sheet2 - Reference
part# side start seq time (min)
1360-1 1st 100 5
1360-1 1st 200 10
1360-1 1st 300 10
1360-1 1st 400 30
1360-1 1st 500 25
1360-1 1st 600 60
1560-3 2nd 100 10
..........

Need urgent help with SUMPRODUCT()
 

Also, I forgot to mention that the values in start/ stop sequence range from
50 to 9999. The side is either 1st side or 2nd side and the part# has both
letter and number and all part# do not have the same number of characters.

"Cam" wrote:

Valko,

I was wondering whether text field or numeric field from Access have
something to do with why it is not calculating or give incorrect calculation.
Just for information on my actual data from Access part# and side and start
and stop seq are text field and time are numeric fields.

Should I change the start and stop sequence to numeric field instead?

"T. Valko" wrote:

Importing data from another application can sometimes lead to importing
unseen characters like leading/trailing spaces that casue problems with
formulas.

Every time I import something into Excel I run this macro to "clean" the
data:

http://www.mvps.org/dmcritchie/excel/join.htm#trimall


--
Biff
Microsoft Excel MVP


"Cam" wrote in message
...
Valko,

Thanks for all your help. I re-entered the data manually and it seemed to
be
working. But, the actual raw data I worked with is from an import Access
database. Some calculated and some give me 0 result. Maybe, there have to
be
something about the character when importing data from Access db. Is there
a
way to inport the data correctly?

"T. Valko" wrote:

See this screencap:

http://img528.imageshack.us/img528/2058/camul0.jpg

As you can see in the screencap the formula returns the correct results.

This is the formula entered in G2:

=SUMPRODUCT(--(A$10:A$16=C2),--(B$10:B$16=D2),--(C$10:C$16=E2),--(C$10:C$16<=F2),D$10:D$16)

There is also a fragmented post from this thread that I replied to.


--
Biff
Microsoft Excel MVP


"Cam" wrote in message
...
Hello,

I have an Excel to track performance. Sheet1 is performance table where
user
input their ID, date, part#, side, start seq & stop seq daily. Then
on a reference sheet called "Ref" with field: part#, side, seq#,
time(min).

I need a formula to put in the column G in sheet1 to calculate the time
based on a match of part#, side, start seq and stop seq of "Ref" sheet.
Thanks

Example:
Sheet1 - User input data
A B C D E F
G
ID date part# side start seq stop seq
Calculated
Time
001 1/8/08 1360-1 1st 100 400 55
(5+10+10+30)
002 1/10/08 1360-1 1st 300 500 65
(10+30+25)

Sheet2 - Reference
part# side start seq time (min)
1360-1 1st 100 5
1360-1 1st 200 10
1360-1 1st 300 10
1360-1 1st 400 30
1360-1 1st 500 25
1360-1 1st 600 60
1560-3 2nd 100 10
..........

Need urgent help with SUMPRODUCT()
 

Should I change the start and stop sequence to numeric field instead?

Try it and see if it makes a difference.

You can try to force those fields to numeric like this:

=SUMPRODUCT(--(A$10:A$16=C2),--(B$10:B$16=D2),--(C$10:C$16+0=E2),--(C$10:C$16+0<=F2),D$10:D$16+0)

However, that won't work if there are unseen characters like
leading/trailing spaces. If there are unseen characters that will cause the
formula to return an error.


--
Biff
Microsoft Excel MVP


"Cam" wrote in message
...
Valko,

I was wondering whether text field or numeric field from Access have
something to do with why it is not calculating or give incorrect
calculation.
Just for information on my actual data from Access part# and side and
start
and stop seq are text field and time are numeric fields.

Should I change the start and stop sequence to numeric field instead?

"T. Valko" wrote:

Importing data from another application can sometimes lead to importing
unseen characters like leading/trailing spaces that casue problems with
formulas.

Every time I import something into Excel I run this macro to "clean" the
data:

http://www.mvps.org/dmcritchie/excel/join.htm#trimall


--
Biff
Microsoft Excel MVP


"Cam" wrote in message
...
Valko,

Thanks for all your help. I re-entered the data manually and it seemed
to
be
working. But, the actual raw data I worked with is from an import
Access
database. Some calculated and some give me 0 result. Maybe, there have
to
be
something about the character when importing data from Access db. Is
there
a
way to inport the data correctly?

"T. Valko" wrote:

See this screencap:

http://img528.imageshack.us/img528/2058/camul0.jpg

As you can see in the screencap the formula returns the correct
results.

This is the formula entered in G2:

=SUMPRODUCT(--(A$10:A$16=C2),--(B$10:B$16=D2),--(C$10:C$16=E2),--(C$10:C$16<=F2),D$10:D$16)

There is also a fragmented post from this thread that I replied to.


--
Biff
Microsoft Excel MVP


"Cam" wrote in message
...
Hello,

I have an Excel to track performance. Sheet1 is performance table
where
user
input their ID, date, part#, side, start seq & stop seq daily. Then
on a reference sheet called "Ref" with field: part#, side, seq#,
time(min).

I need a formula to put in the column G in sheet1 to calculate the
time
based on a match of part#, side, start seq and stop seq of "Ref"
sheet.
Thanks

Example:
Sheet1 - User input data
A B C D E F
G
ID date part# side start seq stop seq
Calculated
Time
001 1/8/08 1360-1 1st 100 400 55
(5+10+10+30)
002 1/10/08 1360-1 1st 300 500 65
(10+30+25)

Sheet2 - Reference
part# side start seq time (min)
1360-1 1st 100 5
1360-1 1st 200 10
1360-1 1st 300 10
1360-1 1st 400 30
1360-1 1st 500 25
1360-1 1st 600 60
1560-3 2nd 100 10
..........

Need urgent help with SUMPRODUCT()
 

I tried it, but still doesn't work. Does the sequence with 50 to 9999
(different size character) caused this issue? I am not sure what else to do.

"T. Valko" wrote:

Should I change the start and stop sequence to numeric field instead?

Try it and see if it makes a difference.

You can try to force those fields to numeric like this:

=SUMPRODUCT(--(A$10:A$16=C2),--(B$10:B$16=D2),--(C$10:C$16+0=E2),--(C$10:C$16+0<=F2),D$10:D$16+0)

However, that won't work if there are unseen characters like
leading/trailing spaces. If there are unseen characters that will cause the
formula to return an error.


--
Biff
Microsoft Excel MVP


"Cam" wrote in message
...
Valko,

I was wondering whether text field or numeric field from Access have
something to do with why it is not calculating or give incorrect
calculation.
Just for information on my actual data from Access part# and side and
start
and stop seq are text field and time are numeric fields.

Should I change the start and stop sequence to numeric field instead?

"T. Valko" wrote:

Importing data from another application can sometimes lead to importing
unseen characters like leading/trailing spaces that casue problems with
formulas.

Every time I import something into Excel I run this macro to "clean" the
data:

http://www.mvps.org/dmcritchie/excel/join.htm#trimall


--
Biff
Microsoft Excel MVP


"Cam" wrote in message
...
Valko,

Thanks for all your help. I re-entered the data manually and it seemed
to
be
working. But, the actual raw data I worked with is from an import
Access
database. Some calculated and some give me 0 result. Maybe, there have
to
be
something about the character when importing data from Access db. Is
there
a
way to inport the data correctly?

"T. Valko" wrote:

See this screencap:

http://img528.imageshack.us/img528/2058/camul0.jpg

As you can see in the screencap the formula returns the correct
results.

This is the formula entered in G2:

=SUMPRODUCT(--(A$10:A$16=C2),--(B$10:B$16=D2),--(C$10:C$16=E2),--(C$10:C$16<=F2),D$10:D$16)

There is also a fragmented post from this thread that I replied to.


--
Biff
Microsoft Excel MVP


"Cam" wrote in message
...
Hello,

I have an Excel to track performance. Sheet1 is performance table
where
user
input their ID, date, part#, side, start seq & stop seq daily. Then
on a reference sheet called "Ref" with field: part#, side, seq#,
time(min).

I need a formula to put in the column G in sheet1 to calculate the
time
based on a match of part#, side, start seq and stop seq of "Ref"
sheet.
Thanks

Example:
Sheet1 - User input data
A B C D E F
G
ID date part# side start seq stop seq
Calculated
Time
001 1/8/08 1360-1 1st 100 400 55
(5+10+10+30)
002 1/10/08 1360-1 1st 300 500 65
(10+30+25)

Sheet2 - Reference
part# side start seq time (min)
1360-1 1st 100 5
1360-1 1st 200 10
1360-1 1st 300 10
1360-1 1st 400 30
1360-1 1st 500 25
1360-1 1st 600 60
1560-3 2nd 100 10
..........

Need urgent help with SUMPRODUCT()
 

Can I see your file? If you can send a copy to me I'll take a look and see
what's going on. I'm at:

xl can help at comcast period net

Remove "can" and change the obvious.

If it's a big file (1mb) then zip (compress) it.

--
Biff
Microsoft Excel MVP


"Cam" wrote in message
...
I tried it, but still doesn't work. Does the sequence with 50 to 9999
(different size character) caused this issue? I am not sure what else to
do.

"T. Valko" wrote:

Should I change the start and stop sequence to numeric field instead?

Try it and see if it makes a difference.

You can try to force those fields to numeric like this:

=SUMPRODUCT(--(A$10:A$16=C2),--(B$10:B$16=D2),--(C$10:C$16+0=E2),--(C$10:C$16+0<=F2),D$10:D$16+0)

However, that won't work if there are unseen characters like
leading/trailing spaces. If there are unseen characters that will cause
the
formula to return an error.


--
Biff
Microsoft Excel MVP


"Cam" wrote in message
...
Valko,

I was wondering whether text field or numeric field from Access have
something to do with why it is not calculating or give incorrect
calculation.
Just for information on my actual data from Access part# and side and
start
and stop seq are text field and time are numeric fields.

Should I change the start and stop sequence to numeric field instead?

"T. Valko" wrote:

Importing data from another application can sometimes lead to
importing
unseen characters like leading/trailing spaces that casue problems
with
formulas.

Every time I import something into Excel I run this macro to "clean"
the
data:

http://www.mvps.org/dmcritchie/excel/join.htm#trimall


--
Biff
Microsoft Excel MVP


"Cam" wrote in message
...
Valko,

Thanks for all your help. I re-entered the data manually and it
seemed
to
be
working. But, the actual raw data I worked with is from an import
Access
database. Some calculated and some give me 0 result. Maybe, there
have
to
be
something about the character when importing data from Access db. Is
there
a
way to inport the data correctly?

"T. Valko" wrote:

See this screencap:

http://img528.imageshack.us/img528/2058/camul0.jpg

As you can see in the screencap the formula returns the correct
results.

This is the formula entered in G2:

=SUMPRODUCT(--(A$10:A$16=C2),--(B$10:B$16=D2),--(C$10:C$16=E2),--(C$10:C$16<=F2),D$10:D$16)

There is also a fragmented post from this thread that I replied to.


--
Biff
Microsoft Excel MVP


"Cam" wrote in message
...
Hello,

I have an Excel to track performance. Sheet1 is performance table
where
user
input their ID, date, part#, side, start seq & stop seq daily.
Then
on a reference sheet called "Ref" with field: part#, side, seq#,
time(min).

I need a formula to put in the column G in sheet1 to calculate
the
time
based on a match of part#, side, start seq and stop seq of "Ref"
sheet.
Thanks

Example:
Sheet1 - User input data
A B C D E F
G
ID date part# side start seq stop seq
Calculated
Time
001 1/8/08 1360-1 1st 100 400 55
(5+10+10+30)
002 1/10/08 1360-1 1st 300 500 65
(10+30+25)

Sheet2 - Reference
part# side start seq time (min)
1360-1 1st 100 5
1360-1 1st 200 10
1360-1 1st 300 10
1360-1 1st 400 30
1360-1 1st 500 25
1360-1 1st 600 60
1560-3 2nd 100 10
..........