Skip to content

How to Count Cells with a Common Word in Excel Quickly?

25 de February de 2022

When it comes to making a list of records, calculations, smart forms, Microsoft Excel is the most recommended and used program for it, even being a mandatory requirement when it comes to getting a job, for this reason it is so insisted that everyone has at least a decent command of the program.

You can download Excel from the official Microsoft website, use its online version or buy the digital disk that comes with the entire Microsoft Office installation package. In this article, we will teach you how to count cells that have the same words as a condition.

What should you consider before counting cells in Excel that have a word in common?

Microsoft Excel itself is an extremely powerful program with which we can carry out various activities, including searching and count certain cells that meet some condition, in this case, frequent words. The =COUNTIF function is one of the basic formulas of the program and it will allow us to count the number of cells, within a range specified by us, that have ONE equal condition.

When using this function, keep the following in mind to make the most feasible and profitable use of the formula:

  • Excel is not case sensitive, the =COUNTIF function is not exempt from this property.
  • You can use wildcard characters like asterisks and question marks, to make searching and counting specific.
  • All the data in your list, record and/or form must be written correctly so that Excel can recognize it without errors.
  • Be sure to open and close parentheses () to prevent formulas from being left open.

searching for common words in excel cells

COUNTIF does not work with strings greater than 255 characters

The formula =COUNT.IF will allow us to search for words in an interval or range of cells that we specify, the problem with this is that it will be limited if such a range has more than 255 characters, remember that both letters, signs, numbers and even blank spaces, count as characters. In this case, we can use the CONCATENATE function, which will allow us to join long strings with others, that is, to ignore the limitation of 255 characters.

How to count cells that have a word in common within Excel?

When you want to keep track of some record you keep, such as the number of people with the same name, there are several functions for it. One of them is the use of formulas, which are:

  • =FIND.
  • =ISNUMBER.
  • =SUM.

The first function will search for all those cells that meet a condition and mark them where they are located, the second, twill return the results to “True” for favorable results and “False” for those that are not; the last function will do the counting. The whole formula would look like this, start using from the inside, to the outside: =SUM(–ISNUMBER(FIND(Condition;Range))).

It is even possible to make or create a word cloud in Excel, to help show the results that we want to be shown with greater relevance.

With the ‘COUNTIF’ function

A reduced form of what was mentioned in the previous section is the one that we previously mentioned, although with its limitations, of course. You can use it manually or automatically depending on how you feel most comfortable.

In the first way, locate yourself in any cell outside your list and write =COUNT.IF, as in the previous case, open parentheses and in the first section you are going to place the range of results to count “(A1:A4)” for example; then you separate by a semicolon and place the condition, in this you can put a characteristic or if you have said condition in a different cell, click on it and Excel will recognize it. Finally, hit “Enter” on your keyboard.

apply functions in excel

The automatic mode is by going to the search bar and clicking on “fx” there you will get the various formulas, choose COUNTIF, a dialog box will appear where you will also place the range and the condition, when you have done so, click on “Accept” and that’s it.

Using Kutools for Excel

If what Microsoft Excel is capable of does not seem powerful enough to you, it is also possible to boost it even more with extensions, in this case, the one we recommend par excellence is “Kutools for Excel”. Among its more than 300 functions, it has the ability to search and countbut now taking into account the color of the background of the cells, font color, their font and others, you can download this extension on its official page.

Why aren’t all cells that have the word in common counted in Excel?

You must take into account the points that we put in previous sections, the =COUNTIF function Although it is very useful, it can present certain errors in its results if the considerations that we have already mentioned are not met, displaying a message of the type #VALUE!, which is when there was a problem.

In this case, it is best to do a smart search in Excel with the use of macros and the VBA, but at this point, it is necessary to know about codes and how to work with them, since they are the fundamental basis of macros and VBA. At this point, a more advanced user experience is required for this.