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]

Is this what you are trying?

Cell B9

=SUMPRODUCT(--(EXACT(B11:AF33,"S")))

Cell C9

=SUMPRODUCT(--(EXACT(B11:AF33,"s")))

Cell D9

=IF(COUNTIF(B11:AF33,"AR")<>0,0,B9+C9)

 

[hsolanki]

Hi Siddharth i have tried your suggestion however it doesn't reset the number back to zero once "ar" is found and it does not start re-calculating all the "S" again

 

[Siddharth Rout]

The moment you type "ar", the count does go to 0. I think I am not able to understand your requirement. See my very first post. See how I created screenshot showing S and AR and showed you the counts? Can you create couple of similar scenarios and post them as screenshots?

For example, see these 3 scenarios. What should be the values that you are expecting to see in "???"