SIMIF Issue

[Diacide]

Hi

I have 2 Workbooks, Book2 acts as a report that pulls data of Book1. Book 1 is an input sheet that a clerk uses. Book1 consists mostly of drop-down select boxes with data validation and some cells to type hour and minute. Now one of the Tabs on Book1 is called "Downtime" with columns "Type", "Machine Number", "Date", "Down Time" (Hr, min), "Description", "Up Time (Hr, min)" "Duration". The values that needs to be returned to Book2 is the "Duration". The logic that needs to be followed is the following:

IF Machine Number = XYZ, Tally up all the duration times for that machine. We want to know the total duration of each Machine. The current formula looks like this, but only works if Book2 is open:

=IFERROR(SUMIF('F:\Reports\[11.xlsx]DS_DT'!$C$6:$C$155,JW$3,'F:\Reports\[11.xlsx]DS_DT'!$N$6:$N$155),0)+IFERROR(SUMIF('F:\Pit Control\[11.xlsx]NS_DT'!$C$6:$C$155,JW$3,'F:\Reports\[11.xlsx]NS_DT'!$N$6:$N$155),0)

When the IFERROR is removed the formula returns #VALUE!. Once the workbook is opened, the correct Summed value is returned. I have read that SUMIF does behave like this. It does not update to closed workbooks. But the strange thing is that it works on a colleague of mine's Laptop. And he also runs Excel 2013. I have re-installed office, installed office 2010, set all security settings on as low as possible, no luck. I even tried it on another PC which is also running Excel 2013. This gave the same result. Below is an example of the data. This would have been simple if the events only occurred once, but multiple break-downs is a real possibly:

[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 1320"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Ref.[/TD]
[TD]Plant Type[/TD]
[TD]Plant no.[/TD]
[TD="colspan: 4"]Booked Off[/TD]
[TD]Breakdown[/TD]
[TD]Repoted to:[/TD]
[TD="colspan: 4"]Booked On[/TD]
[TD]Duration[/TD]
[/TR]
[TR]
[TD]Date[/TD]
[TD]Hour[/TD]
[TD]Minute[/TD]
[TD]Time Stamp[/TD]
[TD]Description[/TD]
[TD]Date[/TD]
[TD]Hour[/TD]
[TD]Minute[/TD]
[TD]Time Stamp[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]Drills[/TD]
[TD]DR-060[/TD]
[TD]9-Jul-14[/TD]
[TD]7[/TD]
[TD]56[/TD]
[TD]7/9/14 7:56[/TD]
[TD]Hooter Problem[/TD]
[TD] [/TD]
[TD]9-Jul-14[/TD]
[TD]8[/TD]
[TD]33[/TD]
[TD]7/9/14 8:33[/TD]
[TD]0.6[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]Excavator[/TD]
[TD]EXC-053[/TD]
[TD]9-Jul-14[/TD]
[TD]7[/TD]
[TD]27[/TD]
[TD]7/9/14 7:27[/TD]
[TD]Oil Leak-Hydraulic[/TD]
[TD] [/TD]
[TD]9-Jul-14[/TD]
[TD]8[/TD]
[TD]22[/TD]
[TD]7/9/14 8:22[/TD]
[TD]0.9[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]Excavator[/TD]
[TD]EXC-052[/TD]
[TD]9-Jul-14[/TD]
[TD]8[/TD]
[TD]14[/TD]
[TD]7/9/14 8:14[/TD]
[TD]Hydraulic Pipe[/TD]
[TD] [/TD]
[TD]9-Jul-14[/TD]
[TD]8[/TD]
[TD]44[/TD]
[TD]7/9/14 8:44[/TD]
[TD]0.5[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]Drills[/TD]
[TD]DR-083[/TD]
[TD]9-Jul-14[/TD]
[TD]7[/TD]
[TD]46[/TD]
[TD]7/9/14 7:46[/TD]
[TD]Electrical Problem[/TD]
[TD] [/TD]
[TD]9-Jul-14[/TD]
[TD]8[/TD]
[TD]23[/TD]
[TD]7/9/14 8:23[/TD]
[TD]0.6[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD]Drills[/TD]
[TD]DR-073[/TD]
[TD]9-Jul-14[/TD]
[TD]7[/TD]
[TD]50[/TD]
[TD]7/9/14 7:50[/TD]
[TD]Rotery head-problem[/TD]
[TD] [/TD]
[TD]9-Jul-14[/TD]
[TD]14[/TD]
[TD]0[/TD]
[TD]7/9/14 14:00[/TD]
[TD]6.2[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD]Dozer[/TD]
[TD]D10-40[/TD]
[TD]9-Jul-14[/TD]
[TD]8[/TD]
[TD]31[/TD]
[TD]7/9/14 8:31[/TD]
[TD]Failing to start[/TD]
[TD] [/TD]
[TD]9-Jul-14[/TD]
[TD]19[/TD]
[TD]0[/TD]
[TD]7/9/14 19:00[/TD]
[TD]10.5[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD]Drills[/TD]
[TD]DR-059[/TD]
[TD]9-Jul-14[/TD]
[TD]8[/TD]
[TD]32[/TD]
[TD]7/9/14 8:32[/TD]
[TD]Engine Cut Off[/TD]
[TD] [/TD]
[TD]9-Jul-14[/TD]
[TD]8[/TD]
[TD]50[/TD]
[TD]7/9/14 8:50[/TD]
[TD]0.3[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD]Drills[/TD]
[TD]DR-058[/TD]
[TD]9-Jul-14[/TD]
[TD]8[/TD]
[TD]51[/TD]
[TD]7/9/14 8:51[/TD]
[TD]Rotery head-problem[/TD]
[TD] [/TD]
[TD]9-Jul-14[/TD]
[TD]9[/TD]
[TD]17[/TD]
[TD]7/9/14 9:17[/TD]
[TD]0.4[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD]Water_Bowser[/TD]
[TD]WB-071[/TD]
[TD]9-Jul-14[/TD]
[TD]8[/TD]
[TD]55[/TD]
[TD]7/9/14 8:55[/TD]
[TD]Failing to start[/TD]
[TD] [/TD]
[TD]9-Jul-14[/TD]
[TD]9[/TD]
[TD]8[/TD]
[TD]7/9/14 9:08[/TD]
[TD]0.2[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD]Dump_Truck[/TD]
[TD]DT-193[/TD]
[TD]9-Jul-14[/TD]
[TD]8[/TD]
[TD]58[/TD]
[TD]7/9/14 8:58[/TD]
[TD]Failing to start[/TD]
[TD] [/TD]
[TD]9-Jul-14[/TD]
[TD]11[/TD]
[TD]19[/TD]
[TD]7/9/14 11:19[/TD]
[TD]2.3[/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD]Drills[/TD]
[TD]DR-059[/TD]
[TD]9-Jul-14[/TD]
[TD]8[/TD]
[TD]50[/TD]
[TD]7/9/14 8:50[/TD]
[TD]Engine Cut Off[/TD]
[TD] [/TD]
[TD]9-Jul-14[/TD]
[TD]13[/TD]
[TD]23[/TD]
[TD]7/9/14 13:23[/TD]
[TD]4.5[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Any help on this problem would be highly appreciated.

 

[Aladin Akyurek]

A formula that cannot process arrays does not work wit closed books.

Try rather one that will do so...

=SUM(SUMPRODUCT(('F:\Reports\[11.xlsx]DS_DT'!$C$6:$C$155=JW$3)+0,'F:\Reports\[11.xlsx]DS_DT'!$N$6:$N$155),
    SUMPRODUCT(('F:\Pit Control\[11.xlsx]NS_DT'!$C$6:$C$155=JW$3)+0,'F:\Reports\[11.xlsx]NS_DT'!$N$6:$N$155))

 

[Diacide]

A formula that cannot process arrays does not work wit closed books.

Try rather one that will do so...

=SUM(SUMPRODUCT(('F:\Reports\[11.xlsx]DS_DT'!$C$6:$C$155=JW$3)+0,'F:\Reports\[11.xlsx]DS_DT'!$N$6:$N$155),
    SUMPRODUCT(('F:\Pit Control\[11.xlsx]NS_DT'!$C$6:$C$155=JW$3)+0,'F:\Reports\[11.xlsx]NS_DT'!$N$6:$N$155))

Thanks, will give it a try and let you know.

 

[Diacide]

Thanks a million, that worked perfectly!

 

[Aladin Akyurek]

Thanks a million, that worked perfectly!

You are welcome. Thanks for providing feedback.