Need urgent help with SUMPRODUCT()

[Cam]

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
...........

 

[T. Valko]

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$16)

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

 

[Cam]

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]

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

 

[Cam]

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?

 

[Cam]

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.

 

[T. Valko]

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$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.

 

[Cam]

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]

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.