Excel – Count text values

It is easy to sum or count a group of numbers in Excel. What is harder is summing or counting a group of words. For example, let’s say you have a column of data that contains “Yes” and “No” and you want to count the number of “No’s”

To perform this task, use a countif function. The countif function is similar to the count function. It has two arguments:

=countif(range, criteria)

where “range” is the range of data in question and “criteria” is the criteria that we are looking for.  For instance, if our range is A1:A6 and our goal is to look for all records that are “no,” then our countif function is =countif(A1:A6, “no”).  The quotes go around the phrase “no” because it is a string.

Let’s do an example to further reinforce this concept:  Assume you have the data below:

A1     Yes
A2     No
A3     No
A4     No

How do we count the number of “No” responses.  The formula is =countif(A1:A4, “No”).  The answer is 3.

NOTE:  If I write =countif(A1:A4, “no”), the result will be 0.  Why is that?  Well, Excel takes anything within quotes to be case sensitive.  Therefore, writing “no” or “NO” is different than writing “No.”