Sometimes, when you have values, texts, and special characters, empty and non-empty cells in your worksheet, you would want to count the cells which they occupy.
Fortunately, Google sheets have inbuilt functions and formulas that can perform this task quickly and efficiently. Each function has its unique case of applications and its respective drawbacks.
In this tutorial, we are going to examine how these functions and formulas work, How To Count the Non-Empty Cells in Google Sheets, where to use them and their required parameters.
How To Count the Non-Empty Cells in Google Sheets.
As said earlier, when you have a combination of empty and non-empty cells, you may want to count the cells they occupy especially when it’s in a large dataset containing hundreds of empty and non-empty cells.
In Google Sheets, there are multiple functions used in counting all sorts of values on the worksheets. The COUNTA function counts the cells with text only, the COUNT function counts the cells with numbers only, and the COUNTIF function counts the based on given criteria or conditions.
Here, we are going to explain the functions mainly used to count non-empty cells and the required steps taken to produce accurate results. The three functions are the COUNTA, SUMPRODUCT and COUNTIF functions.
Using the COUNTA function
This function is employed when you want to count the cells that contain both texts and numbers. The drawback of this method is that if there are special characters in the cell range, they would be counted. The formula used is
=COUNTA(cell range)
Where;
Cell range is the group of selected cells that contain the texts.
Step 1: Enter the formula in the cell you want your answers displayed.
Step 2: Select the cell range including the data you want to count.
Step 3: Press Enter and the total count of words is produced in the cell.
Note that the numbers in the cell range are also included in the result. So, when we only want to count the numerical values in the worksheet, we use the COUNT function with its formula.
=COUNT(cell range)
Step 1: Enter the formula in the cell you want your answers displayed.
Step 2: Select the cell range including the data you want to count.
Step 3: Press Enter and the total count of numbers in the selected range is produced in the cell.
Using the SUMPRODUCT Function.
The SUMPRODUCT function combines with the LEN function to count the non-empty cells in Google sheets.
The LEN function is used to count the length of a string. It is also used to count special characters, letters, punctuation marks and spaces in a sentence written on a worksheet.
The formula takes the format below.
=SUMPRODUCT(LEN(cell range)>0))
Step 1: Enter the formula in the cell you want your answers displayed.
Step 2: Select the cell range including the data you want to count.
Step 3: Press Enter and the total count of characters, numbers and texts in the cell range is counted.
Using the COUNTIF function.
The COUNTIF function operates on given criteria or conditions. For example, if you want to count the numbers that are greater than 1000, you set the criteria that the function should only count numbers greater than a thousand.
This also applies to text. If you want to count how much a word appears in the dataset, you use the COUNTIF function.
The COUNTIF function takes the format below.
=COUNTIF(cell range, “criteria”)
Step 1: Select the cell range containing the cells you want to count.
Step 2: Enter the formula =COUNTIF(A2:B10, “>1000”) into the result cell.
Step 3: Press Enter and the count of numbers above the set criteria is produced.
The top three methods used have been discussed but there is also a shortcut that counts the texts and numbers in the worksheet.
Step 1: Select the cell range containing the texts and numbers.
Step 2: Go to the bottom right of your worksheet. Click on the drop-down arrow there and a tab is produced. It contains the total sum, average, minimum, maximum values, total word count and number count of the selected range are stated there.
This is also an efficient method but isn’t so suitable for large datasets. However, it is easy to operate and understand.
Final Thoughts.
All methods and functions stated above are very efficient methods and can be used to count texts and numbers in large data. Now, you know How To Count the Non-Empty Cells in Google Sheets.
I hope you found this guide helpful. Thank you.
