We will look at a few techniques in this blog post for counting text-filled cells in Google Sheets. It is frequently required to extract certain subsets of data depending on certain criteria when working with enormous datasets in Google Sheets. Data analysis and reporting frequently call for counting cells with text since it enables us to validate the data and produce summary statistics.
Why Do We Count Text-Containing Cells in Google Sheets?
Some of the reasons for counting cells with text in Google Sheets are:
- Data validation: We must ensure that the data is correct and complete when working with a large dataset. We can validate the data by checking that all required fields have been filled in by counting cells with text.
- Data analysis: We frequently need to filter or organize the data in order to better understand it when we are analyzing it. We may more easily locate the precise data subsets we need to work with by counting cells with text.
- Reporting: We frequently have to sum up data in a meaningful way while creating reports. We can create summary statistics or infographics that offer insights into the data by counting cells that contain text.
- Data cleaning: Cells that require modification can be found by counting cells that include text.
How Do I Count Text-Filled Cells in Google Sheets?
Google Sheets offers four alternative methods that can be used to count cells with text:
Method 1: Select All/Specific Cells to Count the Text in Google Sheets
Selecting the cells within Google Sheets with the help of the mouse and the ctrl key is the simplest method to know the exact numbers. We can do this in the following ways:
Scenario 1: Select All Cells at Once
To select all cells at once, simply press “Ctrl+A” and find the keyword “Count” located in the bottom left to get the accurate number of cells that contain text:
We can also select all cells at once by clicking on the small empty block located top-left right above the number 1 as shown in the above screenshot.
Scenario 2: Select Specific Cells Rows/Columns wise
To count cells specifically within single or multiple columns, select that “entire column“. Here, we are going to select the column “B“:
Now, we are going to select “multiple rows” to get the number of cells having text. For that, we will press “Ctrl” key and select the rows “2” and 4“:
Doing so will provide us the accurate number of cells that contain any text.
Method 2: Count Cells with Text using the COUNTIF Function
To count the cells with text, we can use the COUNTIF Function which will count the text-filled cells in a particular range. The general syntax of using the COUNTIF function for counting text-filled cells is:
=COUNTIF(range,"*")
For example, we will use the above-mentioned formula to count the text-fill cells in Google Sheet of “Grocery Details” of range A1:C8:
=COUNTIF(A1:C8,"*")
The total number of cells containing the text(not number) is 10.
Method 3: Using the COUNTA Function
For instance, in the example above, swap out the COUNTIF for the COUNTA to count the cells with text input:
It has counted the cells which are containing numbers as well.
Method 4: SUM function with ARRAYFORMULA
We can use the SUM function with the arrayformula in which we will pass the range of cells in an array. A condition is applied to count the number of cells which are containing the text. To do so, follow the general syntax:
=SUM(ARRAYFORMULA(IF(range<>"",1,0)))
In our example, we will replace the range with “A2:C8” and the result will be:
This will also count the number of cells containing the text whether it is numbers of alphabets.
Conclusion
To count cells with text in Google Sheets, two functions of COUNTIF and COUNTA are used. Also, the arrayformula can be used to count the text-filled cells. This article demonstrates all the mentioned methods with examples for better understanding.
TUTORIALS ON LINUX, PROGRAMMING & TECHNOLOGY
