Counter with dynamic range

%3CLINGO-SUB%20id%3D%22lingo-sub-327034%22%20slang%3D%22en-US%22%3ECounter%20with%20dynamic%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-327034%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20All%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20to%20pick%20your%20brain%20with%20regards%20to%20an%20operation%20I%20have%20to%20built%20in%20excel.%3CBR%20%2F%3E%3CBR%20%2F%3EI%20have%20a%20file%20where%20I%20register%20the%20result%20of%20some%20tests.%20The%20results%20can%20be%3A%20Positive%2C%20Negative%2C%20Suspended.%3C%2FP%3E%3CP%3ESo%20the%20data%20entry%20side%20is%20composed%20of%20two%20columns%3A%3CBR%20%2F%3EColumn%20A%3A%20Date%20-%20Column%20B%3A%20Result%3CBR%20%2F%3E%3CBR%20%2F%3EThen%20I%20want%20to%20set%20up%20a%20report%20where%20I%20can%20count%20the%20number%20of%20positive%20results.%20When%20a%20limit%20value%20is%20reached%20(let's%20say%2010%20positive%20results)%2C%20the%20counter%20should%20start%20from%20zero.%3CBR%20%2F%3E%3CBR%20%2F%3EUp%20to%20here%20I%20managed%20to%20do%20it%2C%20but%20I%20noticed%20the%20following%20issues%3A%3CBR%20%2F%3E-%20If%20I%20reach%20my%20limit%20(10)%20and%20I%20decide%20that%20the%20next%20limit%20value%20shall%20be%2012%2C%20the%20formula%20will%20update%20so%20the%20report%20will%20show%20me%20that%20I%20end%20up%20with%202%20more%20positive%20results%20to%20get%20in%20order%20to%20reach%20the%20goal%2C%20while%20the%20first%20goal%20(10)%20has%20already%20been%20achieved.%3CBR%20%2F%3E%3CBR%20%2F%3EI'm%20attaching%20the%20file%20for%20better%20understanding.%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3EMaybe%20the%20way%20I'm%26nbsp%3Bbuilding%20the%20logic%20of%20my%20sheet%20is%20wrong%2C%20so%20any%20suggestion%20is%20welcomed.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPS.%20I%20would%20prefer%20using%20formulas%20rather%20than%20VBA.%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3EThank%20you%20to%20anyone%20who%20will%20help%20me.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-327034%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Highlighted
Occasional Visitor

Hi All,

 

I need to pick your brain with regards to an operation I have to built in excel.

I have a file where I register the result of some tests. The results can be: Positive, Negative, Suspended.

So the data entry side is composed of two columns:
Column A: Date - Column B: Result

Then I want to set up a report where I can count the number of positive results. When a limit value is reached (let's say 10 positive results), the counter should start from zero.

Up to here I managed to do it, but I noticed the following issues:
- If I reach my limit (10) and I decide that the next limit value shall be 12, the formula will update so the report will show me that I end up with 2 more positive results to get in order to reach the goal, while the first goal (10) has already been achieved.

I'm attaching the file for better understanding.

Maybe the way I'm building the logic of my sheet is wrong, so any suggestion is welcomed.

 

PS. I would prefer using formulas rather than VBA. 

Thank you to anyone who will help me.