reset back to zero if value found ar-CodePudding

i have got an formula on excel whereby it calculate and add all the "s" from a range however is there anyway also to minus same value calculated if "ar" found within the same range

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

=SUMPRODUCT(LEN(C3:C49978)-LEN(SUBSTITUTE(C3:C49978,"s","")))

many thanks in advance

CodePudding user response：

=SUMPRODUCT((LEN(C3:C49978)-LEN(SUBSTITUTE(C3:C49978,"s","")))*(LEN(C3:C49978)=(LEN(SUBSTITUTE(C3:C49978,"ar",""))))) Using the same kind of logic you used I added a condition where the only from values that do not contain "ar" the occurance of s is counted otherwise it's ignored.

CodePudding user response：

Case 2

=SUMPRODUCT(LEN(SUBSTITUTE(C3:C50,"ar","."))-LEN(SUBSTITUTE(C3:C50,"s","")))

Notes

• Suspect you're after case 1 - interested to know, but you have both cases anyway

• Assumes case-sensitive (so 'S' and 'AR' excluded from calcs. To illustrate minor amendment to your function to count 'S' and/or 's' could modify as follows♣ :

=LET(x_,LOWER(C2:C50),SUMPRODUCT(LEN(x_)-LEN(SUBSTITUTE(x_,"s",""))))

Just for fun - an overcomplex variant of this (not recommended, hardly 'parsimonious' as is your function♣):

=SUM(1*(LOWER(LET(x_,TEXTJOIN("",1,C3:C9),FILTERXML("<x><y>"&MID(x_,SEQUENCE(LEN(x_),1,1,1),1)&"</y></x>","//y")))="s"))

♣ requires Office 365 compatible version of Excel