Home

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
mbandera
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.

 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies