Suggestions for an auto-sum problem (that's probably quite simple!)

Hello there. I'm making a simple worksheet for a friends gliding club. It shows how long they've flown for on a list of sequential days and also lists against each day, the number of lost events (ie they couldn't fly for some reason) in two columns, weather and mechanical. (See attached image).

I'm trying to think of a way that I can automatically get the worksheet to calculate 'time since last lost event". Ideally the worksheet would sum the duration listed against each day starting the day after a lost event has been recorded.

I've made it sound quite complicated but I'm sure if you are quite experienced on Excel/VB, you'll probably scoff at how easy this is.

Any help is appreciated, sorry I could only upload an image of the worksheet, I can email a copy of it helps.

John

Oops think my image cut a bit of my text box off, in the example shown, the time since last lost event would be 90 ie 40 + 30 + 20.

It would help if you could post a small file with sample data and expected results.

Ah ok, that's what I was trying to do but my iPhone wouldn't allow me to attach excel files, just pictures. If it helps, the attached image is of my sample data and the expected result is 90.

try ...

=SUMPRODUCT((OFFSET($B$7,MAX(MATCH(999,$C$7:C1000,1),MATCH(999,$D$7:D1000,1)),0,100,1)))

Ranges based on your sample of starting in row 7

The MATCH formula find the row with the last "lost events" in either C or D

You my friend are a genius. It works perfectly, I'm not even going to pretend to understand how it works!

I did a further test and you will get an error condition if there is only 1 in either C or D column. Is this likely to be the case?

Let me know as it means testing each column individually for an error condition.

As for how it works: the MATCH formula find the row were the last 1 in columns C & D is placed and the MAX determines whether its is C or D.

The OFFSET uses the row found to determine where to start adding from (offset from B7).

SUMPRODUCT simply sums the numbers (the 100 allows for 100 rows of data which you can change).)

I've worked through it as I tried to ensure I'm not just blindly pasting your formula in so at least I learn something from it (I love Excel - I'm just very mediocre at it!). I found that to make it work in its current form, I had to insert a couple of dummy values and increase the range looked at accordingly (see attached Excel file - filed yellow where I tweaked).

Well thank you everyone! I demonstrated my new feature to my friend and he decided it wasn't a metric he was bothered about!!!