우리는 이미 COUNTIF 함수를 사용하여`link : / counting-count-cells-that-contain-specific-text [특정 텍스트를 포함하는 셀을 계산하는 방법]`을 배웠습니다. 이 기사에서는 단어가 Excel 범위에 나타나는 횟수를 계산하는 방법을 배웁니다. 즉, Excel 범위에서 단어가 발생한 횟수를 계산합니다.

0033

일반 공식

=SUMPRODUCT(LEN(range)-LEN(SUBSTITUTE(range,word,"")))/LEN(word)

범위 : 특정 단어를 계산하려는 범위입니다.

단어 : 세고 싶은 단어.

예를 들어 어떻게 작동하는지 이해하겠습니다.

예 : 엑셀 범위에서 “행복한”단어를 센다.

여기에 다른 세포에 몇 가지 문장이 있습니다. 해당 범위에서 “행복”이라는 단어의 발생 횟수를 계산해야합니다.

이 수식을 D2 셀에 씁니다.

=SUMPRODUCT(LEN(A2:A4)-LEN(< a href="`link:/office-skill-ko/excel-text-formulas-excel-substitute-function[LEN]`(A2:A4):

this returns count of characters in each cell as an array \{49;27;34}.

Next `link:/office-skill-ko/excel-text-formulas-len-function-in-excel[LEN]`(`link:/office-skill-ko/excel-text-formulas-excel-substitute-function[SUBSTITUTE]`(A2:A4,D1,"")):

The substitute function repaces word in D1 with “” in each cell of range A2:A4. Then Len function returns count of characters from this substituted sentences in an array \{39;22;29}.

`link:/office-skill-ko/excel-text-formulas-len-function-in-excel[LEN]`(D1):

this returns the length of word in D1 which 5 (happy).

Now the formula is simplified to `link:/office-skill-ko/summing-excel-sumproduct-function[SUMPRODUCT]`(\{49;27;34} - \{39;22;29})/5. After subtraction of arrays, SUMPRODUCT has SUMPRODUCT(\{10;5;5})/5. The function adds the array and we get 20/5.

Which gives us our result 4.



===  Counting Case-Insensitive

Since SUBSTITUTE is case sensitive, above formula will ignore any word not having same case, i.e. “Happy”. To make the above formula ignore case, we must change case of each word to case of word we are looking for. This how it’s done.

SUMPRODUCT (LEN (A2 : A4) -LEN (SUBSTITUTE (UPPER (A2 : A4), UPPER (D1), “”))) / LEN (D1)

Now the case doesn’t matter anymore. This function will count each word in D1 irrespective of case.



===  Possible Errors:

Word part of another word will be counted: In this example, if we had word “happyness” (just for example, I know there’s no word as this) it would have been counted too. To avoid this you could have surrounded the words with speces, “ ” &D1& “ ”. But when word appears first or last in sentence, this will fail too.



===  Related Articles:

`link:/office-skill-ko/counting-count-characters-in-a-cell-in-excel-2[Count Characters in a Cell in Excel]`

`link:/office-skill-ko/counting-counting-the-number-of-values-between-two-specified-values-in-a-list[Counting the Number of Values between Two Specified Values in a List in Microsoft Excel]`

`link:/office-skill-ko/counting-count-cells-that-contain-specific-text[Count Cells that contain specific text in Excel]`

`link:/office-skill-ko/excel-text-formulas-counting-the-number-of-cells-containing-text[Count Cells With Text in Excel]`



===  Popular Articles

`link:/office-skill-ko/keyboard-formula-shortcuts-50-excel-shortcuts-to-increase-your-productivity[50 Excel Shortcut to Increase Your Productivity]`: Get faster at your task.

These 50 shortcuts will make you work even faster on Excel.

`link:/office-skill-ko/formulas-and-functions-introduction-of-vlookup-function[How to use the VLOOKUP Function in Excel]`: This is one of the most used and popular functions of excel that is used to lookup value from different ranges and sheets.

`link:/office-skill-ko/tips-countif-in-microsoft-excel[How to use the COUNTIF function in Excel]`: Count values with conditions using this amazing function. You don't need to filter your data to count specific values. Countif function is essential to prepare your dashboard.

`link:/office-skill-ko/excel-formula-and-function-excel-sumif-function[How to use the SUMIF Function in Excel]`: This is another dashboard essential function. This helps you sum up values on specific conditions.