I set up a table to keep track of engine running hours and other bits of information; that covers a month at a time, one of the functions of the table is to calculate how many hours since the last maintenance. To keep a running total through the month and it resets to zero if maintenance is performed, each row is a day and the daily hours are calculated and imputed in cells in column D, It checked out ok, I then set up iferrors and the ilk, then I remembered to check and see if it was human prof, by just inputting data on only the dates/rows like somebody might do. Then I started my spiral down into the abyss, changing testing and never getting to where I want to be. Admittedly on occasion I’ve gotten side tracked while trying to find the answer and seeing something that would enhance other parts of the whole thing. Have found lots of great stuff in YouTube excelisfun, where he’s mentioned this site many times so I thought I’d check y’all out I’ve gone through quite a few pages and post trying to find something similar no luck so far.
I tried to fix the formula to correct this but have met varied success none giving the desired result.
If I input on row 7 and 8 then there skip inputs until row 10 and as well we did maintenance so from row 10 we need to sum everything below that.
Using match to look in column “I” looking for “maint” the formula should add the hours in column D from row 11 down, if maint doesn’t show up then it needs to add from the start cell down. The formula in H8 and below now is:
“=IFERROR(IF(ISNUMBER(D8), IF(ISNUMBER(MATCH("maint",$I$7:I7,0)),(0+$D8)+$H$7:H7, $H$4+SUM($D$7:D8)),""),"H4 missing")”
Orginal formula was “=IFERROR(IF(D7=0,"",IF(ISNUMBER(D7), IF(ISNUMBER(MATCH("maint",$I7:$I7,0)), IF(ROW(D7)>MATCH("maint",$I7:$I7,0)+6, SUM(INDEX($D7:$D7,MATCH("maint",$I7:$I7,0)+1,1):D7), $H$4+SUM($D7:D7)), $H$4+SUM($D7:D7)),"")),"H4 missing")”
Lost and confused, Ideas are welcome. Currently using 2010, I want the formula to be able to look at the rows above and calculate from the last row that has maint in it, if rows are skipped it needs to ignore them but still sum the rest.
Thanking all for your time in advance!
I tried to fix the formula to correct this but have met varied success none giving the desired result.
If I input on row 7 and 8 then there skip inputs until row 10 and as well we did maintenance so from row 10 we need to sum everything below that.
Using match to look in column “I” looking for “maint” the formula should add the hours in column D from row 11 down, if maint doesn’t show up then it needs to add from the start cell down. The formula in H8 and below now is:
“=IFERROR(IF(ISNUMBER(D8), IF(ISNUMBER(MATCH("maint",$I$7:I7,0)),(0+$D8)+$H$7:H7, $H$4+SUM($D$7:D8)),""),"H4 missing")”
Orginal formula was “=IFERROR(IF(D7=0,"",IF(ISNUMBER(D7), IF(ISNUMBER(MATCH("maint",$I7:$I7,0)), IF(ROW(D7)>MATCH("maint",$I7:$I7,0)+6, SUM(INDEX($D7:$D7,MATCH("maint",$I7:$I7,0)+1,1):D7), $H$4+SUM($D7:D7)), $H$4+SUM($D7:D7)),"")),"H4 missing")”
Lost and confused, Ideas are welcome. Currently using 2010, I want the formula to be able to look at the rows above and calculate from the last row that has maint in it, if rows are skipped it needs to ignore them but still sum the rest.
Thanking all for your time in advance!