We are often in a situation where we can use the same formula for various things or different scopes. There are also situations where we need some things done in Excel, but there are different ways to get the results we need.
In the example, below we will show how to count the appearance of a certain value in a cell or a column.
Count Value Appearance in a Column
For our example, will use the list of NBA players:
If we want to find out the appearance of the word „James“ in column A, we will insert the following formula in cell B2:
|
1 |
=COUNTIF(A2:A28,"*James*") |
We will simply use our existing range, and we will input asterisks at the beginning and at the end of the word as a wildcard that replaces any text before and after the word itself.
The final result will be as follows:
We know that this is true since we have three occurrences of the word “LeBron James” and three occurrences of the word “James Harden”.
If we delete the asterisk at the beginning of the word, we will have three occurrences of a word:
As this formula:
|
1 |
=COUNTIF(A2:A28,"James*") |
Only counts the words that have James at the beginning of the sentence, and any value after that, which means it calculates three values for “James Harden”.
Count Value Appearance in a Cell
Now let say that we have the same list of NBA players, but located in only one cell- C1:
We now want to know how many times the word „Paul“ appears in this cell. We will use the following formula to achieve this:
|
1 |
=SUM(LEN(C2)-LEN(SUBSTITUTE(C2,"Paul","")))/LEN("Paul") |
This formula does the following:
Calculates the number of text strings in our cell (in our case it is 357). Then it replaces all the words where the word “Paul” is located with an empty string. The word “Paul” has four letters in it, so we will have a total of 12 letters less (as we have the word Paul appearing three times). This means that the second part of the formula has a total of 345.
When we subtract those numbers (357-345) we will have a number 12.
Then we need to divide that number by a total number of letters in the word “Paul”, which is the number 4.
Once we do that, we will have this result:
We ger number three, which is the correct number of times this word appears in cell C1.
Count values using “Find and Replace”
Another way to find cells in a column or range that meet certain criteria is to use the “Find and Replace” tool.
To count cells using this tool, follow these steps:
- select cells you want to check.
- Use the Ctrl + F keyboard shortcut.
- Click the “Find All” button.
In the bottom left corner, there is information on how many cells with the word “James” are there.
While inside a window, use the Ctrl + A shortcut to highlight all of them.
