How to Count Nonblank Cells in Google Sheets

- Written by Puneet

excelchamps-free-courses-puneet-gogia

Let’s say you manage a team and have a spreadsheet tracking completed tasks for the week. Each team member logs their completed tasks daily. Now, you need to count how many days each person logged at least one task. You count the nonblank cells in each person’s row using Google Sheets.

To help you understand this, I have a sample workbook with a few values in a column where a few cells are blank, and now you need to count the cells with a value.

There are multiple methods to write formulas for this, and we will learn all of them individually.

Use COUNTA for Count Nonblank Cells

This is the easiest way to count cells that are not blank. COUNTA counts how many cells in a range are not empty. It includes cells with text, numbers, or any other data. Its syntax is simple; you must refer to the range and hit enter to get the result.

When you enter the function, it returns 6 in the result, meaning you have 6 cells in the range with a number, a text, or other value.

Notes: If your data contains errors, COUNTA will count those cells. You can also combine multiple ranges to count nonblank cells. Cells with logical values like TRUE or FALSE are counted as non-empty.

Using COUNTIF to Count Non-Empty Cells

Other than using the COUNTIF function, you can also use count apps to count cells that are not blank. It counts the number of cells that meet a specific condition. You can specify a range and a condition, and it counts how many cells match that condition.

Here, you can specify a range and then specify a condition to count a cell that has a value. In the example below, we refer to the range A1:A10, and in the criteria, we use a not equal to operator sign and a blank value using double quotation marks.

You can see in the right area we have used & to combine <> and “”. This tells the function that we are looking for the cells that are not equal to the blank value, which means those that are not blank.

=COUNTIF(A1:A10,"<>"&"")

When you hit enter, it returns 6 the cells count with the value in the range A1:A10.

Using COUNTIFS Instead of COUNTIF

While using both formulas that we have discussed above, you might face a problem when there is white space in a cell, and both of the formulas we have used above will count that cell with white space as a nonblank cell.

To overcome this problem, you have two different methods or, I would say, two different ways to write formulas; the first is to use COUNTIFS instead of COUNTIF. And the second is to use the SUMPRODUCT function. Here’s the formula with the COUNTIFS:

=COUNTIFS(A1:A10,"<>"&"",A1:A10,"<>"&" ")

This formula counts the number of non-empty cells in the range A1 to A10, excluding cells with just a space. It checks two conditions: the cell is not empty (“<>”&””), and the cell is not just a space (“<>”&” “).

Using SUMPRODUCT Method

The problem that we discussed while switching to count from count can also be solved by using the SUMPRODUCT.

SUMPRODUCT multiplies corresponding numbers in two or more ranges and then adds up those results. It combines multiplication and addition in one step, making this function useful for calculating totals.

The formula that you can use is:

=SUMPRODUCT(--(LEN(TRIM(A1:A10))>0))

TRIM(A1:A10) removes any extra spaces from the cells, and LEN(TRIM(A1:A10))>0 checks if the length of each cell’s content is greater than zero. The — converts TRUE/FALSE results into 1s and 0s. Finally, SUMPRODUCT adds up these 1s, giving the count of cells with actual data.

Create Custom Function

If you don’t like to write a formula repeatedly, you can also use Google Apps script 2 gate custom function that counts nonblank cells and ignores white spaces from the range. Below is the code for a custom function.

/**
 * Custom function to count non-empty cells in a range, ignoring white spaces.
 * @param {Range} range - The range of cells to count.
 * @return {number} The count of non-empty cells.
 * @customfunction
 */

function COUNT_NON_EMPTY(range) {
  // Initialize count
  let count = 0;
  // Loop through each cell in the range
  for (let i = 0; i < range.length; i++) {
    for (let j = 0; j < range[i].length; j++) {
      let cellValue = range[i][j];
      // Check if the cell is non-empty and does not contain only white spaces
      if (cellValue && cellValue.toString().trim() !== "") {
        count++;
      }
    }
  }
  return count;
}

To use this for sharing, go to “Extensions” and then click ‘App Scripts’. After that, paste this code into the code window and save it. Now, return to the sheet, enter this function in a cell, and refer to the range from which you want to count the non-empty cells.