If Cell Contains Specific Text Then Add 1 in Excel (5 Examples)

Whenever you want to search for any specific text to do some operations you can easily do it in Excel. In this article, I’m going to show you in Excel that if a cell contains specific text then add 1.

To make the explanation clearer, I’m going to use a sample dataset that represents information about a particular employer regarding salary and rank. The dataset contains 4 columns: Employee Name, Rank, and Salary.

Sample Dataset of Excel If Cell Contains Specific Text Then Add 1


Download Practice Workbook


5 Ways to Add 1 If Cell Contains Specific Text in Excel

1. Using IF Function to Add 1 If Cell Contains Specific Text

By using the IF function you can easily find out if the cell contains any specific text or not then you can add 1 by simply using the arguments of the IF function.

Steps:

To begin with,

  • First, select any cell of your choice to place your resultant value.
  • Here, I’ve selected cell E5.
  • Then, type the following formula in cell E5 or into the Formula Bar.
=IF(B5="Adam Smith",C5+1, C5 )

Using IF Function to Add 1 If Cell Contains Specific Text

Here, in the IF function, I used a condition in the logical_test to check if the cell contains the specific text “Adam Smith” if it does then it will add 1 to the Rank of that particular employee otherwise Rank will remain as it is.

  • Now, press ENTER to add 1 if the cell contains the specific text, Adam Smith.

Using IF Function to Add 1 If Cell Contains Specific Text

Read More: How to Sum If Cell Contains Specific Text in Excel (6 Ways)


2. Combining IF & ISTEXT Functions to Add 1 If Cell Contains Specific Text

You also can use the combination of IF and ISTEXT functions to add 1 if the cells contain specific text.

Steps:

To start with,

  • First, select any cell of your choice to place your resultant value.
  • Here, I’ve selected cell E5.
  • Then, type the following formula in cell E5 or into the Formula Bar.
=IF(ISTEXT(B5),C5+1, C5 )

Combining Excel IF & ISTEXT Functions to Add 1 If Cell Contains Specific Text

Here, the ISTEXT function will check whether the cell value is text or number. Then, the IF function will check the logical_test and If the cell value of the B5 cell is text then it will add 1 to the cell value of C5 to Upgrade the Rank. Otherwise, the Rank will remain as before.

  • Finally, press ENTER to add 1 if the cell contains the specific texts.

  • Now, you can use the Fill Handle to AutoFill formula for the rest of the cells.

Combining Excel IF & ISTEXT Functions to Add 1 If Cell Contains Specific Text

Read More: If Cell Contains Text Then Add Text in Another Cell in Excel


Similar Readings


3. Employing IF & COUNTIF Functions to Add 1 If Cell Contains Specific Text

If you want, you also can use the combination of IF and COUNTIF functions to add 1 if the cells contain specific text.

Steps:

To start with,

  • First, select any cell of your choice to place your resultant value.
  • Here, I’ve selected cell E5.
  • Then, type the following formula in cell E5 or into the Formula Bar.
=IF(COUNTIF(B5, $B$5), C5+1, C5)

Employing Excel IF & COUNTIF Functions to Add 1 If Cell Contains Specific Text

Formula BreakDown

  • COUNTIF(B5, $B$5)—-> The COUNTIF functions count cells in a particular range to fulfill one condition.
    • Output: 1
  • IF(1, C5+1, C5)—-> Here, the IF function will check the condition if it is TRUE then will return value_if_true otherwise will return value_if_false.
  • IF(TRUE, C5+1, C5)—-> As it’s TRUE it will Add 1.
    • C5+1
    • 10+1
    • Output: 11
    • Explanation: The cell contains the specific text in the B5 cell that’s why it Added 1 to Rank.

  • Now, press ENTER to get the result.

  • Here, you can use the Fill Handle to AutoFill formula for the rest of the cells.

Employing Excel IF & COUNTIF Functions to Add 1 If Cell Contains Specific Text

Read More: Excel If Cell Contains Text Then Return Value (8 Easy Ways)


4. Using SUM & IF Functions to Add 1 If Cell Contains Specific Text

Here, I’m going to use the combination of IF and SUM functions to add 1 if the cells contain specific text.

Steps:

To start with,

  • First, select any cell of your choice to place your resultant value.
  • Here, I’ve selected cell F6.
  • Then, type the following formula in cell F6 or into the Formula Bar.
=SUM(IF(B5:B12="Adam Smith",1,0))

Using Excel SUM & IF Functions to Add 1 If Cell Contains Specific Text

Here, the IF function will search the specific text Adam Smith within the range B5:B12 then it will Add 1 for each match through the SUM function.

  • Now, press ENTER to get the resultant value.

Read More: Sum If a Cell Contains Text in Excel (6 Suitable Formulas)


5. Using Combined Functions to Add 1 If Cell Contains Specific Text

In case you want to check multiple specific texts then you can use the IF function, the OR function, and the COUNTIF function to add 1.

Here, I’m going to use two specific texts these are Adam and Kate.

Using Combined Functions to Add 1 If Cell Contains Specific Text

Steps:

To begin with,

  • First, select any cell of your choice to place your resultant value.
  • Here, I’ve selected cell E5.
  • Then, type the following formula in cell E5 or into the Formula Bar.
=IF(OR(COUNTIF(B5, "*"&$G$8&"*"), COUNTIF(B5, "*"&$H$8&"*")), C5+1, C5)

Formula BreakDown

  • COUNTIF(B5, “*”&$H$8&”*”)—-> The COUNTIF function counts cells in a particular range to fulfil one condition.
    • Output: 0
  • COUNTIF(B5, “*”&$G$8&”*”)—-> becomes
    • Output: 1
  • OR(COUNTIF(B5, “*”&$G$8&”*”), COUNTIF(B5, “*”&$H$8&”*”))—-> turns into
  • OR(1, 0)—-> The OR function will be TRUE if any of the conditions meets.
    • Output: TRUE
  • IF(OR(COUNTIF(B5, “*”&$G$8&”*”), COUNTIF(B5, “*”&$H$8&”*”)), C5+1, C5)—-> becomes
  • IF(TRUE, C5+1, C5)—-> The IF function will return value_if_true if the logical_test is fulfilled or TRUE otherwise will return value_if_false.
    • C5+1
    • 10+1
    • Output: 11
    • Explanation: Added 1 for the specific text.

  • Press ENTER to add 1 if the cell contains specific text.

Using Combined Functions to Add 1 If Cell Contains Specific Text

  • Here, you can use the Fill Handle to AutoFill formula for the rest of the cells.

Read More: Excel Formula If Cell Contains Text Then Return Value in Another Cell


Practice Section

You can practice the explained methods in the practice section.


Conclusion

In this article, I have shown 5 methods of Excel if the cell contains specific text then add 1. These ways will help you to simplify your task. Feel free to comment down below for any types of queries and suggestions.


Related Articles

Shamima Sultana

Shamima Sultana

Hello! Welcome to my Profile. I am Shamima Sultana Rita. Currently, I'm working as a Project Manager at ExcelDemy. I am doing research on Microsoft Excel and here we will be posting articles related to this. My last educational degree was BSc and my program was in Computer Science and Engineering from East West University-Bangladesh. I am a Computer Science graduate with a great interest in research and development. I love to learn new things. Data Analysis is one of my favorite fields as I love to extract patterns based on problems. Here, I'm trying to provide enriched quality content regarding Excel basics to Advanced. Always try to gather knowledge from various sources and try to make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

5 Excel Hacks You Never Knew

Genius tips to help you unlock Excel's hidden features

FREE EMAIL BONUS

ExcelDemy
Logo