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