Shamima Sultana
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.
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 )
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 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 )
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: If Cell Contains Text Then Add Text in Another Cell in Excel
Similar Readings
- How to Return Value If Cells Contain Certain Text from a List
- Find text in an Excel range & return cell reference (3 ways)
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: 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))
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.
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: 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.
