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

Get FREE Advanced Excel Exercises with Solutions!

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


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

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.

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

Using IF Function to Add 1 If Cell Contains Specific Text

Read More: How to Check If Cell Contains Text Then Return Value in Excel


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: Excel If Cell Begins with Certain Text Then Return Value


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: How to Return Value in Excel If Cell Contains Text from List


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: Check If Cell Contains Text Then Return Value with Multiple Conditions in Excel


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: If Cell Contains Text Then Add Text in Another Cell in Excel


Practice Section

You can practice the explained methods in the practice section.


Download Practice Workbook


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

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
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

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo