How to count the occurrences of a character or keyword in Excel

Excel solves a lot of problems in financial statements, which greatly improves our work efficiency and saves working time. and can be quality and quantity of the completion of cumbersome financial work. Not only in the financial application, in the student era, we will also use in the ordinary examination of Excel Office software.

Problem: The original data range is A1:A5, each cell contains different or identical text, and the sum of the occurrences of a character or keyword in each cell text depends on the requirement.

Answer: The next step is to answer the question.

1, the original data range is A1:A5, each cell contains different or the same text, depending on the requirements of a character or keyword in each cell text in the sum of the number of occurrences.

2, first of all, ask "Beijing" The number of occurrences (the entire cell content equals "Beijing"). We enter the formula: =countif (A1:A5, "Beijing") results return 1 because only A1 cells in the source data are "Beijing".

3, then, find the A1:a5 text contains "Beijing" the number of cells in the keyword. We enter the formula: =countif (A1:A5, "* Beijing *") results return 2, because A1 and A3 all contain the word "Beijing".

4. Finally, find the total number of "Jing" characters in the A1:a5 cell text. We enter the formula: =sumproduct (LEN (A1:A5)-len (Substitute (A1:a5, "Jing", ")") returns 3 because the three cells in A1,a3 and A4 contain the word "Beijing". If a cell contains two "Beijing" characters, it will also be counted.

5, the following specific explanation of the 4th step in the formula usage. First look at the substitute () function. The substitute () function syntax is this: substitute (the source data, the content to be replaced, the result of the substitution). For example: Substitute (A1, "Jing", "") means to replace all the "Beijing" words in A1 with empty.

6, Len () function returns the length of a text, such as Len (A1) returns A1 cell contents are several characters.

7. LEN (Substitute (A1, "Jing", "")) returns the result is A1 cell text replaces the word "jing" with the length of the text after it is empty.

8, A1 cell original character length minus the "Beijing" word to replace the empty text length, the result is the "Beijing" word in A1 cell text appears in the total number of times.

9, finally, with the Sumproduct function will a1:a5 in each of the "Beijing" word number added and the "Beijing" word in the original A1:a5 appeared in the total number of times.

The above is a small series of how to use Excel to count the number of occurrences of a character or keyword in Excel statistics of a character or keyword appears in the number of graphics and text tutorials, hope to help everyone!