How to Count Specific Words in a Column in Excel

In the sample dataset, count “happy” and “life” in C5:C10.

Method 1 – Combine Functions to Count Specific Words in a Column

Steps:

Select D15 and enter the following formula.

=SUMPRODUCT(LEN(C5:C10)-LEN(SUBSTITUTE(C5:C10,$C$12,"")))/LEN($C$12)+SUMPRODUCT(LEN(C5:C10)-LEN(SUBSTITUTE(C5:C10,$C$13,"")))/LEN($C$13)

Press Enter button.

This is the output.

Formula Breakdown

There are two parts in the formula: for word 1 and word 2.
LEN(C5:C10)
- Output: {63;58;74;104;44;114}.
- returns the string lengths.
LEN(SUBSTITUTE(C5:C10,$C$12,””))
- Output: {58;58;74;104;34;114}.
- The SUBSTITUTE function replaces “happy” in the cell range. The LEN function returns its length in an array.
LEN($C$12)
- Output: 5.
- returns the length of word 1.
The first part reduces to -> SUMPRODUCT({5;0;0;0;10;0})/5
- Output: 3.
- counts the number of “happy” in the column. 5+10=15, then 15/5=3.
The second part reduces to -> SUMPRODUCT({4;0;8;0;0;0})/4
- Output: 3.
returns 3+3=6.

Method 2 – Count Specific Words in a Column Ignoring Cases

Steps:

Enter the following formula in D15.

=SUMPRODUCT(LEN(C5:C10)-LEN(SUBSTITUTE(UPPER(C5:C10),UPPER($C$12),"")))/LEN($C$12)+SUMPRODUCT(LEN(C5:C10)-LEN(SUBSTITUTE(UPPER(C5:C10),UPPER($C$13),"")))/LEN($C$13)