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:

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

<tbody>
</tbody>

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.