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