# Calculate from last instance ignore blanks to maintain a running total.

#### MerkDolf

##### New Member
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.

### Excel Facts

Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Excel 2010
ABCDEFGHI
1LAST ENTRY PREVIOUS YEAR HOURS SINCE OIL CHANGE:100 HOURSDecember LAST running hours below
2RUNNING TIMEFUELOILHOURS SINCE1000
401-Jan1000N
502-Jan100010022N102
603-JanN
704-JanN
805-Jan100210042N104
Sheet1

A little more research, and testing and I’ve located the error of my ways, you must pay attention to the rules of the MATCH function, “The values in the lookup_array argument must be placed in ascending order”. So by only using one word with other words descending from that one, and trying to get past blanks, the order wasn’t ascending.

For those of you who took the time in the first place to look at this thank you.

Replies
5
Views
267
Replies
2
Views
316
Replies
7
Views
229
Replies
1
Views
423
Replies
15
Views
173

1,196,507
Messages
6,015,601
Members
441,904
Latest member
edris Alsatouf

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back