Sometimes for better visualization or any other reasons, your workbook may have blank cells. But in the calculation process or monitoring data, you have to count only not blank or non-empty cells. Show
In this article, I will focus on 6 useful methods to count cells that are not blank in Excel. Download Practice WorkbookHow to Count Cells that are Not Blank in ExcelIn the following illustration, we see vegetable Item and producing Country. But there exist some blank or empty cells. But we have to count not blank cells for calculation. And we can utilize the following methods.  1. COUNTA FunctionThe COUNTA function shows the number of cells that are not empty. It also considers error values, text, numbers, formula, empty texts (= “”) in counting the number of cells. The syntax of the function is: =COUNTA (value1, [value2], …) Here, the arguments may be cell references, items or cell ranges. Steps:
2. COUNTIF FunctionThe COUNTIF function counts the number of cells that match the single condition. Counting cells with dates, numbers, and texts are used by the COUNTIF function. More importantly, this function ignores formulas though the COUNTA function counts cells containing any formula. The COUNTIF function syntax is =COUNTIF (range, criteria) Here, range refers to the range of cells and criteria means any condition. If you want to count not blank cells with numbers or any types of value just follow the process. Steps:
3. COUNTIFS FunctionIf you are looking to apply multiple criteria for a corresponding range, then COUNTIFS will be the right choice. Though we are talking about counting non blank cells only, you can apply the function in the dataset. The syntax is: =COUNTIFS (range1, criteria1, [range2], [criteria2], …) Here range1, range2 refers to first and second cell range respectively and criteria1, criteria2 refers to conditions for first and second cell range. Steps:
4. COUNTBLANK FunctionWell, the COUNTBLANK function is mainly used for counting blank or empty cells in range. But you can easily count non blank cells by subtraction from the product of ROWS and COLUMNS. ROWS and COLUMNS return the number of rows and columns in a reference or array. The syntax of three functions are Here, range and array mean the cell range basically. In the following dataset, there are some blank cells. Using the function, we will get the not blank cells. Steps:
5. SUMPRODUCT FunctionCOUNTA and COUNTIF consider any empty text, formula, error values, etc., as not blank cells, but the cell looks blank. For example, the C9 cell in the following dataset has no appearance though = “” etc. are available in the cell. In that case, using SUMPRODUCT might be effective The syntax of the SUMPRODUCT function is =SUMPRODUCT(array1, [array2], [array3], …) Here array refers to the cell range that you want to multiply and add. We can apply the function for our dataset like the following Steps:
The output shows that the number of not blank cells using SUMPRODUCT is 20 while for COUNTA is 21 that means C9 is not counted in the case of SUMPRODUCT. The same output will be found using a combination of SUMPRODUCT and LEN functions. LEN can count all special characters, non-printable characters and all spaces from an excel cell. The syntax of LEN is The LEN function finds the number of characters for each cell in the range, therefore SUMPRODUCT counts the cells that contain at least one character. The SUMPRODUCT then returns an array with True and False. The ‘–‘ converts the True to 1 and False to 0. The SUMPRODUCT then sums the values and returns the result. Steps:
6. Find & Replace ToolThe standard Excel dialog Find and Replace allows you to count non blank cells as well. It is a good method in the case of a large dataset. Additionally, all the values and their cell addresses are shown in one window. Furthermore, by clicking its name in a list, you can easily navigate to any item. Now, you may proceed with the following steps. Steps:
Note: You may use CTRL+F as a Shortcut Key for the above process
Things to Keep in MindBe careful about the file name, the file location, and also about the extension file of Excel. Always follow the syntax during typing the formula in a cell. You have to be cautious about brackets. Above the massage shows the typo error in a formula ConclusionHopefully, the above six methods might be fruitful for counting non-blank cells in excel. Thanks for reading this article. If you have any queries, please share below. Further Readings
How do you count nonblank cells in Excel?Use a special Excel formula to count all non-blank cells. Select any empty cell in your sheet.. Enter =counta() or =ROWS() * COLUMNS()-COUNTBLANK() to the formula bar.. Then you can manually enter the range address between the brackets in your formula. ... . Press Enter on your keyboard.. What function counts non blank cells in a range?Use the COUNTA function to count only cells in a range that contain values. When you count cells, sometimes you want to ignore any blank cells because only cells with values are meaningful to you.
How do you count nonblank cells in VBA?If you want to count only nonblank cells, you can use this formula =COUNTA(A1:G11) (the range A1:G11 indicates the range you want to count the nonblank cells from, you can change it as you need), or you also can use this formula =COUNTIF(A1:G11,"<>").
What function counts the number of blank cells?Use the COUNTBLANK function, one of the Statistical functions, to count the number of empty cells in a range of cells.
|
