Hello,
My excel file collects information from another excel file, where I
have three columns: start_time, end_time, and another cell that is
suppose to contain a name of the person. Within one range (3-250) I
need to subtract end_time from start_time and see if it's less than
16
minutes, if so, if the third cell, does not contain the name, I need
to count all the occurances and record the number into the cell of
the
correct file.
I cannot quite figure out how to perform subtraction within the range
(in this case: column G - column F):
=SUMPRODUCT(--(('path[FileA.xls]Tab1'!G3:G250 [...???...]
TIME(0,15,59))*(LEN('path[FileA.xls]Tab1'!Q3:Q250)=0)))

Thanks a ton for your advice!

See my reply in microsoft.public.excel

In the future, please don't multi-post: you can address one message to
multiple groups, and that will prevent confusion like this.

Bernie


"Lenchik" wrote in message
...
Hello,
My excel file collects information from another excel file, where I
have three columns: start_time, end_time, and another cell that is
suppose to contain a name of the person. Within one range (3-250) I
need to subtract end_time from start_time and see if it's less than
16
minutes, if so, if the third cell, does not contain the name, I need
to count all the occurances and record the number into the cell of
the
correct file.
I cannot quite figure out how to perform subtraction within the range
(in this case: column G - column F):
=SUMPRODUCT(--(('path[FileA.xls]Tab1'!G3:G250 [...???...]
TIME(0,15,59))*(LEN('path[FileA.xls]Tab1'!Q3:Q250)=0)))

Thanks a ton for your advice!

Try it like this:

I'm assuming "path" is a placeholder.

=SUMPRODUCT(--('path[FileA.xls]Tab1'!G3:G250-'path[FileA.xls]Tab1'!F3:F250TIME(0,15,59)),--('path[FileA.xls]Tab1'!Q3:Q250=""))

--
Biff
Microsoft Excel MVP


"Lenchik" wrote in message
...
Hello,
My excel file collects information from another excel file, where I
have three columns: start_time, end_time, and another cell that is
suppose to contain a name of the person. Within one range (3-250) I
need to subtract end_time from start_time and see if it's less than
16
minutes, if so, if the third cell, does not contain the name, I need
to count all the occurances and record the number into the cell of
the
correct file.
I cannot quite figure out how to perform subtraction within the range
(in this case: column G - column F):
=SUMPRODUCT(--(('path[FileA.xls]Tab1'!G3:G250 [...???...]
TIME(0,15,59))*(LEN('path[FileA.xls]Tab1'!Q3:Q250)=0)))

Thanks a ton for your advice!

Assuming your times are in Col A&B and Names in Col D
then in C1 enter
=B1-A1 and copy down...

then in the other file enter this in, say, A1
=SUMPRODUCT(--(('PATH[FileName.xls]TABNAME'!$C$1:$C100)<TIME(0,16,00)),
--(('PATH[FileName.xls]TABNAME'!$D$1:$D$6)<"D1"))
and copy down
To get my email id paste my address in an Excel cell and press Enter...
Adjust 100 to your range

"Lenchik" wrote:

Hello,
My excel file collects information from another excel file, where I
have three columns: start_time, end_time, and another cell that is
suppose to contain a name of the person. Within one range (3-250) I
need to subtract end_time from start_time and see if it's less than
16
minutes, if so, if the third cell, does not contain the name, I need
to count all the occurances and record the number into the cell of
the
correct file.
I cannot quite figure out how to perform subtraction within the range
(in this case: column G - column F):
=SUMPRODUCT(--(('path[FileA.xls]Tab1'!G3:G250 [...???...]
TIME(0,15,59))*(LEN('path[FileA.xls]Tab1'!Q3:Q250)=0)))

Thanks a ton for your advice!