Counting case sensitive words:
In this tip, I will show you how to count words, with or without taking the case into account.

In Excel, we all know the “COUNTIF” function.
This counts the number of non-blank cells in a range that meets the specified criterion.
In the formula here, we use it for words:
Where, a cell in the range (A2: A8), satisfies the criterion in the cell (B1), then the count is displayed in the cell C1.
So, I have seven cells in the range where the word ‘apple’ occurs .
Whether this apple is typed with a capital letter or not, does not matter for the COUNTIF function.
This function is not case sensitive.

We can still distinguish between the same words written with or without the case, by using the SUM function with the EXACT function.

We start the formula as always with the “equals sign” (=), type SUM and the open parentheses (.
Then we type the function AS and once again open the brackets (.
The EXACT function takes two arguments. The first argument is a cell, the second is the condition.
Instead of selecting cells for the first argument, we type or select an entire range, such as A2: A8.

Attention: When we select a range as argument, when only one cell is expected, we are talking about an array formula.

Then we type a comma (,), followed by the second argument B1 (The condition) and close the brackets ).
Then multiply (*) this with 1. Close the brackets ) again and press ‘Ctrl+ Shift+ Enter’ on the keyboard.
You must close an array formula by pressing the ‘ Ctrl+ Shift+ Enter’ keys.
This places curly braces around the formula (1).

As you can see in the image below, only the words Apple are added and not apple.

Change your criterion in cell B1 to apple (not capitalized ), and the number in cell C1 is adjusted.

Let me explain why.
The function will compare the values of the cells in the range and evaluate to TRUE or FALSE.
TRUE is equal to 1, FALSE is equal to 0 (zero).
These values, 1 or 0, are multiplied by 1 after the conclusion of the “AS”.
And we all have learned, 1X1 = 1, 1×0 = 0.
So what this formula does is:
(TRUE + FALSE+ FALSE + TRUE+ TRUE + TRUE + TRUE)* 1
or
(1 +0 +0 +1 +1 +1 +1) * 1
which gives 5.

FYI.
To view the values of the “AS” function, whether TRUE or FALSE (A), select this function in the formula bar (B), and press the F9 key on your keyboard.

Featured Courses

• Downloading iPad Apps
• Lesson 1: Introduction to Publisher
• Lesson 65: File Formats