How to count the number of times a word / number appears in a range?
When you work on an Excel worksheet, have you ever tried to count the number of times a word or a number appears in a range of cells? Counting them one by one will be time-consuming if there are multiple cells needed to be counted, today, I will talk about some good tricks for you to solve this task in Excel.
The following array formula can help you to count the number of times the specific word appears in a cell or a range. Please do as follows:
1. Click a cell where you want to get the result, C2, for instance, and then enter this formula: =(SUM(LEN(A1:A3))-SUM(LEN(SUBSTITUTE(A1:A3,"KTE",))))/LEN("KTE"), see screenshot:
2. Then press Ctrl + Shift + Enter keys together to get the correct result:
1. In above formula, A1:A3 is the data rang you want to count the specific word from, and KTE is the word you want to count. You can change them as you need.
2. This formula is case sensitive, it also can count the number of times the specific word appears in a cell.
The long formula may be difficult to remember, so, you can use a handy tool – Kutools for Excel, with its Count times a word appears feature, you can quickly get the number of the certain word in a range or a cell.
|Kutools for Excel : with more than 300 handy Excel add-ins, free to try with no limitation in 60 days.|
After installing Kutools for Excel, please so with following steps:( Free Download Kutools for Excel Now )
1. Click a cell which you want to put the counting result.
2. Click Kutools > Formulas > Count times a word appears, see screenshot:
3. In the Formula Helper dialog box, click the first button besides the Text to select the cells that you want to count the specific word, and then enter the word that you want to count into the Word box, (the word you entered should be around with the double quotations), see screenshot:
4. After setting the arguments, click Ok button, and the result has been displayed in the selected cell. See screenshot:
Note: This function is also case sensitive, it can count the number of times the specific word appears in a cell as well.
Click to know more about this Count times a word appears feature.