How to add 1 to a specified cell if the cell contains certain text in Excel

Consider that you have a list of names in which one name is repeated multiple times. Now you need to scan the column for the name which is repeated multiple times and add 1 if another repeated name is entered in the specific cells to show the total number of the repeated name.

Below shows an example of how to do this.

Add 1 to a specified cell if the cell contains certain text with an array formula

The array formula below can help you add 1 to a specified cell if a cell range contains certain text in the Excel workbook.

Step 1

Below is the Excel workbook with a few names in it. The name “Caroline” is repeated multiple times in the workbook as shown in the below screenshot.

Step 2

Select the blank cell in the Excel workbook, and the formula =SUM(IF(A2:A10="Caroline",1,0))+ Ctrl + Shift + Enter kindly follow the below screenshot.

Here, A2:A10 is the column range that contains the text that you will count. You can change it as per your desire.

If you see the above screenshot the name Caroline is populated in the selected cell which shows 4. This means the name Caroline is repeated 4 times in the Excel workbook.

You can also add a filter to the name in the excel workbook and if you know the particular name which is repeated multiple times you can filter it by using the filter option. Click on the name which you want and unselect the rest of the name. But this will be easy only when the list of names is very few or less than 15.

Using this array formula, you can find the multiple names count in an Excel workbook easily and this is the easiest method to find out if you have a huge list of names in your Excel workbook.

Things to Remember

• Careful when you write the formula in the Excel workbook.

• Insert the data ranges correctly and carefully into the formulas.