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
22 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 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
28 Replies
PacketMon Components are not loading in WAC 1909
HotCakeX in Windows Admin Center on
2 Replies