reset count value if certain value found within range cell

[hsolanki]

Hi Experts

i was wondering if there any way to reset the value back to Zero if certian value found for example

i have got an formula on excel whereby it calculate and add all the "S" from a range "B11:AF33" however is there anyway also back to 0 value if "ar" found within the same range "B11:AF33"

below it is the formula that counts all the "S" and add the result in to cell number "B6"

=SUMPRODUCT(LEN(B11:AF33)-LEN(SUBSTITUTE(B11:AF33,"s","")))

 

[Siddharth Rout]

Sorry I am slightly confused. I can interpret your question in two ways. Can you help me understand which one is it or if you want something else completely?

You want to restart counting after an AR is found. For example

OR, if AR is present then count = 0. No recounting.

If it is the latter then a simple formula can solve your issue

=IF(COUNTIF(B11:AF33,"AR")<>0,0,COUNTIF(B11:AF33,"S"))

 

[hsolanki]

Morning Siddharth thank you so much and sorry for late reply. only issue is that i want only to count the "ar" within the same range only if there is "ar" found in the range you current suggestion is also counting the ar when ar it is not found for example. when i type S 2 times it is showing and counting as 3 in total instead of 2 S. you suggestion it does works however somehow it is also counting the "ar" in the background with the "S"

 

[Siddharth Rout]

The formula that I gave checks if there is any AR or not. If there is no AR, then it counts only S. If there is even 1 AR then it returns 0. If this is not what you want then can you show couple of examples on what you are trying to achieve?

 

[hsolanki]

Morning Siddhartha thank you for quick reply. i am trying to archive is that for example if you record all the S in the range cell B11:AF33 then it counts all the S in the cell B6.
now i would also like to set to Zero for the all the S counts if "ar" found within the same range.

below it is my sample book

Sample Candy file V1..xlsm

2021 M.S.C = M,Staff Candy File Over time = O,BS,Email Status Lateness = L,0,NAME HERE Sickness = S,0,Not Sent,ERN Number: Absent = A,0,Date of joining Holiday = H 0,0,0 Recovery = 0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19 JANUARY FEBRUARY MARCH APRIL:235.20 MAY JUNE JULY AUGUST SEPTE...

docs.google.com

 

[hsolanki]

Hi Siddharth sorry i was wondering is there any way around to get the same result using the VBA code other than formulas ? with your current formulas suggestion somehow it adding an extra "S" even if there is no "ar" is found.

your current suggestion formulas it is exactly how i would like it to work however only problem is that it adding invisible extra numbers

 

[Siddharth Rout]

I always recommend using NON VBA methods. Only if there is no or better alternative then I recommend the VBA. Anways I checked the file and I do not see where you have put the formula? If you are referring to sheet "2023" then the formula will return 0 as there is 1 AR in that range. if you delete the AR then the formula return 5 as expected.

 

[hsolanki]

Hi Siddharth i have placed the formula on cell no: B9 and i have entered Capital "S" and lower "s" and it is recording as 2 S instead of 1 as i would only would like to record the capital "S" on the number counts and if "ar" found every time it reset back to Zero

see attached my sample book

Thank you

 

[hsolanki]

Hi Siddharth basically what i am trying to achieve it is that whenever you enter Capital "S" and lower "s" in to the range cell it add up all the number for example Number of Total capital "S" and lower "s" which it will give you total number of Sickness. total 12 sickness days S & s and 2 different sets (12x2x2=48) in the B6 Cell however now when ever "ar" it is found in the cell the number value in the "B6" cell must reset back to zero

i have attached another sample book with some playing around with your formula suggestion, i get an error in the "B6" cell however if i put "ar" in any range cell then it reset back to Zero. i need similar to something like but it should be coutning all the Capital S and Lower S in the cell number D9

sorry if i am confusing

Master File - Sample.xlsm

2022 M.S.C = M,Staff Candy File Over Time = O,Bradford Score Lateness = L,0,NAME HERE Sickness = S,ERN Number:,Days / Hours Booked Absent (AWOL) = A,0,Date of joining Holiday = H Recovery = 0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19 JANUARY FEBRUARY MARCH,S,s APRIL:235.20 MAY JUNE,S,ar,S,s

docs.google.com

Thank you

 

[hsolanki]

Hi Sorry i have managed to set the value to back to Zero however how do i start adding the number again once the "ar" is found i would like to start adding back all the "S" in the range cell

=IF(COUNTIF(B11:AF33,"ar")<>0,1,SUMPRODUCT(LEN(B11:AF33)-LEN(SUBSTITUTE(B11:AF33,"S",""))))