CODEWITHSUNDEEP
excelif-statementexcel-formula
fhaney
fhaney

Reputation: 89

IF Statement for Range of Cells that Contain Specific Text

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:

Example of 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 any way to create this formula?

Upvotes: 1

Views: 1690

Answers (1)

BigBen
BigBen

Reputation: 50162

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

enter image description here

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

Upvotes: 2

Related Questions