Count number of empty cells since last non-empty cell

Here's the setup I'm working with.

Excel.PNG

It's a work out plan. Rows are dates, columns are exercises. Every time I complete the exercise "back" I insert a cross at the respective date.

I need a way to count the rows since the last time I did a particular exercise. In the example, if it were currently 9/16 the solution should yield 9 days for the exercise "back", 1 day for the exercise "front" and 8 days for the exercise "oh". I should be able to apply the solution on the whole column "back". There might be several check marks in one column, I am only interested in the number of empty rows since the last check mark.

Any help? Thanks.

1st, Please upload a sample of your workbook, not a picture of your data. Pictures are pretty much impossible to edit, and no-one wants to re-type your data for you
Also, not all members can upload picture files (Company firewalls and stuff) - and, depending on what browser is being used, some pics dont even show up on the forum

Your workbook should show a small desensitized example of the data you are working with and a manual mockup of the expected results.

2nd, do you have to use letters, could you use a number (like 1?)
3rd, do you only enter the date on that day, or are they copied down past "today"? (cant see the pic)

Thanks for the file, it makes things much easier

I added a row between the main heading and the exersizes, so the date can show there, and I changed teh "x" to 1 so that I could use this, copied across...
=IFERROR(INDEX($A$4:$A$30,MATCH(0*1,B4:B30,-1)),"")

regarding my comment "3rd, do you only enter the date on that day,..." I meant that do you enter the date each day, or put the dates in for the whole month (at the start of the month), but if you use numbers instead of letter, it doesnt matter

Jason, nice 1 - but you dont need the TODAY() part, just =LOOKUP("y",B$4:B$30,$A$4:$A$30)
(I extended it to take 2 extra days for a 31-day month)

Also, dontr know why I had 0*1 in my formula...
=IFERROR(INDEX($A$4:$A$30,MATCH(0*1,B4:B30,-1)),"")
It just needs to be...
=IFERROR(INDEX($A$4:$A$30,MATCH(0,B4:B30,-1)),"")

@ rluesc, if you want to stay with letters, use Jason's suggestion

Nope you are not missing anything, I am - I did not include the "days since last done" part

Originally Posted by jason.b75

That said, theory always works well until you apply it to reality.

Like the old addage goes - if the facts dont conform to the theroy...change the facts

=IFERROR(TODAY()-INDEX($A$4:$A$30,MATCH(0,H4:H30,-1)),"")

I used this method to calculate days since last time exercise was done. This is an array formula entered in a row inserted at row 2. Enter in B2 and fill across.

Formula:

=TODAY()-INDEX($A4:$A1200,MAX(IF(B4:B1200="x",ROW(B4:B1200)))-3)

Enter with Ctrl + Shift + Enter

This will allow the table to be extended down to row 1200 to give you a little over 3 years of dates to be entered without doing anything other than filling in the data.