Reseting a COUNTIF Statement

[matthewnsarah07]

Hi

I think what I'm thinking of is possible but I can't work out how!!

I have a worksheet which is tracking the number of times a uniform is washed so it can be replaced when needed.

Between columns E:BD the user simply enters the letter W when they have washed their uniform and a simple COUNTIF in Column BE keeps a total. When this total reaches 5 a replacement item will be ordered and at that point the letter R will be inserted to show a replacement.

This is my sticking point, at the point an R is entered I want the COUNTIF to start from 0 again and so on each time a R is entered - can I do this within excel?

Thanks for your help

 

[NBVC]

Can you post a small sample workbook to show what setup you have and how you are entering these "R's"?

 

[matthewnsarah07]

I've attached a quick sample

In this the letter W for wash, after the 5th the item is replaced and an R for replacement is entered. The counter (BE) would then need to count from the furthest R from the left each time

Thanks

 

[NBVC]

If there are always 5 "W's" and then an "R", then perhaps this?

=COUNTIF(D3:BD3,"W")-COUNTIF(D3:BD3,"R")*5

If you are not consistently 5 "W" and one "R", then try:

=IF(COUNTIF(D3:BD3,"R"),COUNTIF(INDEX(D3:BD3, MATCH(2,INDEX(1/(D3:BD3="R"),0))):BD3,"W"),COUNTIF(D3:BD3,"W"))

 

[matthewnsarah07]

That works fine as the number of washes is a constant.

Thanks for your help, really appreciated!!

 

[NBVC]

No problem, I added another formula in case the washes are not constant...