Sumifs with Three Conditions

[desoriente]

I need to sumif a column of numbers only if it meets three criteria and export the answer into another excel file.

<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
First Excel File (the final format I need):
A B C D
1 Date Begin Time End Time Total
2 12/1/2010 22:00 22:59
3 12/1/2010 23:00 23:59 (formula in D3?)
4 12/2/2010 00:00 00:59

<o></o>
Second Excel File (has the data to look up):
A B C
1 Date Times Numbers
2 12/1/2010 22:59 0.01
3 12/1/2010 23:00 0.01
4 12/1/2010 23:30 0.01
5 12/1/2010 23:59 0.01
6 12/2/2010 00:00 0.01
<o></o>
Both excel files are shorten here for simplicity. In realty, the second excel file’s Column A has the entire month of December, and Column B has every minute within a 24 hour period.

So, for example, I need a final total (sum) in D3 in the first excel file based on these conditions: In the second excel file, if Column C falls between the hours 23:00 and 23:59 (Column B) on 12/1/2010 (Column A), sum up those particular numbers, which would be 0.03. This is what I came up with, but to no avail.
<o></o>
=SUMIFS(C2:C6,A2:A6,"='J:\FirstExcelFile.xlsx]Sheet1'!$A$3",B2:B6,"=<'J:\FirstExcelFile.xlsx]Sheet1'!$B$3&=>'J:\FirstExcelFile.xlsx]Sheet1'!$C$3")

Nothing happens. Please help!

 

[DonkeyOte]

SUMIFS (like SUMIF etc) will not work with closed targets (implied by full path in your sample formula) and you will need to revert to the less efficient SUMPRODUCT/Array alternative

Assuming the full paths in your example are reversed incorrectly, ie formula is in First File not Second File [and thus B2:B6 etc should be fully qualified and not the criteria]

D3
=SUMPRODUCT(('J:\SecondExcelFile.xlsx]Sheet1'!$A$2:$A$6=$A3)*('J:\SecondExcelFile.xlsx]Sheet1'!$B$2:$B$6>=$B3)*('J:\SecondExcelFile.xlsx]Sheet1'!$B$2:$B$6=$C3),'J:\SecondExcelFile.xlsx]Sheet1'!$C$2:$C$6)

However you will far better served running an External Pivot Table with both Date & Time as Row Fields (the latter grouped by Hour) and Numbers as Data Field.

 

[DonkeyOte]

edit: just noticed I copied the reference construct from your original and they are missing a bracket

References to:

'J:\SecondExcelFile.xlsx]Sheet1'

should read

'J:\[SecondExcelFile.xlsx]Sheet1'