Formula to count days since last accident

[KingSmono]

I have a list of dates in Column A, and either "Yes" or "No" in Column B, indicating whether or not there was a work-related accident on that day. Every day, a Yes or No is placed into Column B, next to that day's date.

Example:

01/04/2012 No
01/05/2012 Yes
01/06/2012 No
01/07/2012 Yes
01/07/2012 No
01/09/2012 No
01/10/2012 No
01/11/2012 No
01/12/2012 No
01/13/2012 No
01/14/2012
01/15/2012
01/16/2012

I am trying to create a formula that counts the number of "No's" under the last "Yes"... So in the above example, the result of the formula would be a 6. I.E. "There have been 6 days without a work-related accident." And as soon as a "Yes" is entered, the count goes back to zero. If I were to replace the last "Yes" in my example above with a "No" - the result of the formula would be 8.

I'm trying to do this without utilizing VBA or custom functions... Only with the built-in excel functions. Thanks in advance for any assistance!

[Haseeb Avarakkan]

Try this,

=IF(COUNTIF(B:B,"yes"),COUNTIF(INDEX(B:B,LOOKUP(2,1/(B2:B400="yes"),ROW(B2:B400))):INDEX(B:B,65536),"No"),0)

[KingSmono]

Try this,

=IF(COUNTIF(B:B,"yes"),COUNTIF(INDEX(B:B,LOOKUP(2,1/(B2:B400="yes"),ROW(B2:B400))):INDEX(B:B,65536),"No"),0)

That worked perfectly! Thank you so much!

[CTI Safety Guy]

I have a new challenge for someone...

I work for a company with multiple sites.

I too am trying to count the number of days since the sites last accident, recordable, property damage, near miss,
first aid, etc.

Here is an example of the data set:

Incident Date Facility Location Incident Type Incident Type-Specific
1/1/2015 Atlanta Accident Recordable
1/2/2015 Charlotte Near Miss First Aid
1/3/2015 Chicago Accident Property Damage
1/4/2015 Cleveland Near Miss No Injury or Property Damage
1/5/2015 Dallas Accident Recordable
1/6/2015 Denver Near Miss First Aid
1/7/2015 Indianapolis Accident Property Damage
2/1/2015 Los Angeles Near Miss No Injury or Property Damage
2/2/2015 Miami Accident Recordable
2/3/2015 New York Near Miss First Aid
2/4/2015 Phoenix Accident Property Damage
2/5/2015 San Francisco Near Miss No Injury or Property Damage
2/6/2015 Seattle Accident Recordable

What function do I need to use to search the data set for keywords like "Accident" and "Recordable", then check the date of that last incident and tally the number of days since?

Example

No. Days since last Accident No. Days since last Near Miss No. Days since last Recordable... etc.
Atlanta
Charlotte
Chicago
Cleveland
Dallas
Denver
Indianapolis
Los Angeles
Miami
New York
Phoenix
San Francisco
Seattle

I appreciate any assistance that you can give.

Thank you,

[melcoats]

Thanks Haseeb A,

That solution did work very well, however I have a follow on question. Your solution is great for counting a single column, but how could the sum of the previous year be carried over to another column for tracking a following year? I've tried several variations and failed miserably. Any advice?

[AliGW]

Administrative Note:

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread