Due to its versatility, Excel is used in thousands of different ways. One example of that versatility is the ability to show us how many cells contain certain types of data. When I’m counting cells with text, I use the COUNTA function.
Table of Contents
I use Excel to track how many articles I’ve written about specific software, the topics I’ve covered in The Techie Senior Tipsheet, and much more.
I’ll show you how I do that. You’ll be able to take my example and use it in your own way.
Why count cells that contain text in Excel?
By using the COUNTA function to count cells with text, I can tell at a glance how many articles I’ve written on a certain subject.
I have an Excel workbook that includes a sheet which tracks how many articles I’ve published on a large number of topics. One column contains the article titles.
I have other columns that tell me the dates the specific articles were published and the date that I discussed them in the Tipsheet.
At the top of the columns, I place functions which count the number of cells with dates in them. If the number of articles matches the number of publication dates, then I know that all of the articles have been published.
If the number of dates in the Tipsheet column matches the number of publication dates, I know that I’ve covered all of that group’s published articles in the Tipsheet.
However, if the number in the Tipsheet column is fewer than the number in the Date Published column, I know that there is at least one article in that group that I could share with my Tipsheet readers.
How to count cells with text in Excel
The COUNTA function in Excel can be used with more complex arguments than it takes for this exercise. In the worksheet that I mentioned earlier, I use the it just to count cells that are not empty.
The function does exactly that. If there is a date, a word, an error, or even a stray decimal point, COUNTA will count that cell.
Locate the COUNTA function in Excel for Windows or Macs
Use the following instructions to locate the COUNTA function in Excel for Windows or Macs.
- Click inside the cell where you would like the number of non-empty cells to display.
- Open the Formulas tab.
- The COUNTA function is hidden in the list of Statistical functions. Click on the More Functions button.
- A short list of function categories will appear. Click on Statistical.
- You should be presented with a long list of functions. Click on COUNTA.
Set up the COUNTA function in Excel for Macs
Now, use your mouse to select the cells that you want to count. For example, if you want to count all of the cells that contain dates in column R, rows 2 – 20, select rows 2-20 in column R.
Use one of the following to activate the function:
1) In Excel for Macs, when you insert a function in a cell, the Formula Builder panel will open as a right sidebar. The range of cells that you selected earlier will be filled in. Click the Done button in the Formula Builder to save and activate the function.
2) Place your cursor at the end of the formula in the Formula Bar (the blank area just above the columns headers). Hit the Return key.
Note: If you are one who avoids dragging a mouse to select cells, you can also enter the cell range manually. If you were counting cells with text in the first ten rows of column F, you would type F1:F10 in the Formula Builder. Then click the Done button.
You can also type the same cell range in the empty parentheses in the Formula bar, then hit the Return key.
With either of those methods, look in the cell where you placed the COUNTA function earlier. You should see a number that indicates how many of the cells in the target range that you selected contain text.
Set up the COUNTA function in Excel for Windows
In Excel for Windows, either of the following will activate the function:
1) In Excel for Windows, when you insert a function in a cell, the Function Arguments panel will open in the center of the screen. The range of cells that you selected earlier will be filled in. Click the OK button in the Function Arguments panel to save and activate the function.
2) Place your cursor at the end of the formula in the Formula Bar (just above the lettered column headers). Hit the Enter key.
The simplest way to set up the COUNTA function in Excel for Windows or Mac
The easiest method of all to use the COUNTA function in Excel is to simply type the function in the cell where you want the count to display.
There’ll be no cells to select or drag your mouse over, no Format Builder or Function Arguments panel.
Just click your mouse in one cell, type a few characters into the Formula bar, and be done with it.
In this example, you want the number of cells that contain text in Column R, Rows 2-20 to be displayed as a number at the top of Column R in Row 1.
In that case, place your cursor in Column R, Row 1 (R1). Then, in the Formula bar just above the column labels, type in =COUNTA(R2:R20). Hit the Enter or Return key.
If you look in cell R1, you should a number indicating the number of cells that contain text in the range R2 – R20.
Change the actual cell reference (R2:R20) to fit your situation.
If you enter text in any cells in the range R2-R20, you should see those cells counted in R1.
A final word about functions in Excel
There are tons of functions and formulas available in Excel. Many of them are designed to help you manage complicated data sets. Some are there to help you do simple tasks such as converting a range of text from lower to uppercase or automatically fill dates in Excel.
You can also access Microsoft’s official documentation on using formulas and functions in Excel.
