# reset count value if certain value found within range cell

#### hsolanki

##### Board Regular
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","")))

### Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
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

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

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"

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?

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

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

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.

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

#### Attachments

• formula S.jpg
162.1 KB · Views: 7
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

Thank you

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",""))))

Replies
8
Views
145
Replies
23
Views
651
Replies
0
Views
174
Replies
1
Views
120
Replies
1
Views
424

1,203,663
Messages
6,056,626
Members
444,879
Latest member
suzndush

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back