IF Statement for Range of Cells that Contain Specific Text-CodePudding

I wanted to create a formula whereby it can automatically return a statement if the range contains the certain text.

Here's example of my data:

And here is the code that I'm using:

=IF(COUNTIF(I2:O2,"B*"),"B",IF(COUNTIF(I2:O2,"A*"),"A",IF(COUNTIF(I2:O2,{"B*","A*"}),"Both","Others")))

It was run perfectly for the first and second statements, but not the third statement whereby if the range contains text start with B* and A*, then it will return "Both".

Is there anyway to create this formula?

CodePudding user response：

Change the logic up slightly by checking for Both first:

=IF(COUNTIF(I2:O2,"B*"),IF(COUNTIF(I2:O2,"A*"),"Both","B"),IF(COUNTIF(I2:O2,"A*"),"A","Others"))

Another option, using LET:

=LET(a,COUNTIF(I2:O2,"A*"),b,COUNTIF(I2:O2,"B*"),IF(AND(a,b),"Both",IF(a,"A",IF(b,"B","Others"))))