=SUMPRODUCT((('[File A.xls]Tab1'!G3:G250-'[File
A.xls]Tab1'!F3:F250)<TIME(0,16,0))*1)
works for me.
Not that is will return the #value! error if any of the values in tab1 F:G
are not numbers - so it appears that something in column F is not a number,
since the formual with just G still works.
HTH,
Bernie
MS Excel MVP
Thanks a lot for your response, Bernie...
somehow, if I just do:
(('[File A.xls]Tab1'!G3:G250-'[File A.xls]Tab1'!F3:F250)<TIME(0,16,0)
I get #value! back...
Both of the columns G and F are both formatted as date.
If I do:
(('[File A.xls]Tab1'!G3:G250)<TIME(0,16,0)
I get a numerical value.
=SUMPRODUCT((('[File A.xls]Tab 1'!G3:G250-'[File A.xls]Tab
1'!F3:F250)<TIME(0,16,0))*('[File A.xls]Tab 1'!Q3:Q250=""))
If there are no spaces in the file or sheet names:
=SUMPRODUCT((([FileA.xls]Tab1!G3:G25-[FileA.xls]Tab1!F3:F25)<TIME(0,16,0))*
([FileA.xls]Tab1!Q3:Q25=""))
Enter the formula while FileA is open, and when you close it, the formula
will change to include the path:
=SUMPRODUCT((('C:\Excel\[FileA.xls]Tab1'!G3:G25-'C:\Excel\[FileA.xls]Tab1'!
F3:F25)<TIME(0,16,0))*('C:\Excel\[FileA.xls]Tab1'!Q3:Q25=""))
HTH,
Bernie
MS Excel MVP
news:798cd203-16d2-46bc-bffd-ebd519efa29b@a19g2000pra.googlegroups.com....
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!
Hi Burnie,
it's really odd...
I am using RDC to connect to connect to a system running Microsoft
Windows Server 2003, I have Excel 2003 installed on it.
So I had created 2 test files: Test1 and Test2.
Test1 has data in 3 columns A, B, C.
Column A contains End_date, Column B contains Start_Date (both
columns
are formated as dates) and Column C contains Names.
Test2 contains formula in A1:
=SUMPRODUCT(--(('C:\Documents and Settings\mylogin\My Documents\
[Test1.xls]Sheet1'!A1:A250-'C:\Documents and Settings\mylogin\My
Documents\[Test1.xls]Sheet1'!B1:B250)>TIME(0,16,0))*('C:\Documents
and
Settings\mylogin\My Documents\[Test1.xls]Sheet1'!C1:C250=""))
and it works flawlessly!!!
However the actual file that I am gathering data from is located on a
different server, with a path looking like:
\\fileserver\level1\level2\level3\level4\[My File.xls]MySheet'!
F3:F250
and it gives me #value! error anyway I use:
('\\\fileserver\level1\level2\level3\level4\[My File.xls]MySheet'!
F3:F250-'\\\fileserver\level1\level2\level3\level4\[My
File.xls]MySheet'!G3:G250)>TIME(0,16,0)
I checked, both of the columns are formatted as dates... through the
whole range 3:250. There are 30 cells in both F and G that are
actually filled with dates and when
I reduce the range to just the cells that are filled (F3:F30, G3:G30),
the subtraction formula works, but when
I use a bigger range: 3:250, somehow, - formula doesn't work... I
tried using trim and still no luck...
I tried :
=SUMPRODUCT(--((('\\\fileserver\level1\level2\level3\level4\[My
File.xls]MySheet'!F3:F250-'\\\fileserver\level1\level2\level3\level4\
[My File.xls]MySheet'!G3:G250)>TIME(0,16,0)*('\\\fileserver
\level1\level2\level3\level4\[My File.xls]MySheet<>"")*('\
\dnafiles1\cit\CIT_CORe\Weekly_Data\HD-Alerts\[HD-Alert
Processor.xls]HD-Alerts'!G3:G250<>""))))
I tried (len('\\\fileserver\level1\level2\level3\level4\[My
File.xls]MySheet'!F3:F250-'\\\fileserver\level1\level2\level3\level4\
[My File.xls]MySheet'!F3:F250)>0)*(len('\\\fileserver
\level1\level2\level3\level4\[My File.xls]MySheet'!F3:F250-'\\
\fileserver\level1\level2\level3\level4\[My File.xls]MySheet'!
G3:G250)>0)*(('\\\fileserver\level1\level2\level3\level4\[My
File.xls]MySheet'!F3:F250-'\\\fileserver\level1\level2\level3\level4\
[My File.xls]MySheet'!G3:G250)>TIME(0,16,0))
First two statements return correct result, but as soon as I add my
"subtraction" I get #N/A!
Also, if I do: '\\\fileserver\level1\level2\level3\level4\[My
File.xls]MySheet'!F3:F250>TIME(0,16,0) or '\\\fileserver
\level1\level2\level3\level4\[My File.xls]MySheet'!
F3:F250>TIME(0,16,0) - they both return the result...
I don't quite understand why subtraction doesn't work and it really
bothers me...
Is there some setting or preference in Excel I do not know about?
Thanks a ton!
Lena
