count occurence of values individually and as a group in excel row-CodePudding

o/p is the required column which is how many times D and P occured in a row either individually or as a group i.e if D and P occurs continuously, it is counted as 1. I am pretty sure there must be a formula for this somewhere only it is difficult to phrase the right words to search it. Hence this question here. Looking for a formula that does the job.

CodePudding user response：

Give the following a try, assuming it's either "U", "D" or "P":

Formula in A1:

=BYROW(B1:L4,LAMBDA(a,IFERROR(COLUMNS(TEXTSPLIT(CONCAT(a),"U",,1)),0)))

Above works for newest versions of Excel, for older CSE-required versions of Excel try:

=SUM(N(FREQUENCY(IF(B1:L1<>"U",COLUMN(B1:L1)),IF(B1:L1<>"U",0,COLUMN(B1:L1)))>0))

CodePudding user response：

If U can be any text or character other than P or D then the following should work:

=LEN(TEXTJOIN("",1,0,MMULT({1,1},--({"D";"P"}=B2:L2))))-LEN(SUBSTITUTE(TEXTJOIN("",1,0,MMULT({1,1},--({"D";"P"}=B2:L2))),"01",2))

Or using Office 365 this spills the result:

=LET(t,BYROW(B2:L12,LAMBDA(b,TEXTJOIN("",1,0,MAP(b,LAMBDA(m,--(OR(m={"D";"P"}))))))),
DROP(REDUCE(0,t,LAMBDA(x,y,VSTACK(x,LEN(y)-LEN(SUBSTITUTE(y,"01",2))))),1))

Both formulas convert the cells to 0 (FALSE) or 1 (TRUE) if the cell equals P or D. The results of one row are joined with a leading 0 and SUBSTITUTE looks for each occurrence of 01 in given string and replaces these 2 characters by 1. If we then compare it to the stringlength prior to the substitute, we have the answer to your question.