How to Count Cells in Google Sheets (4 Easy Ways)

Being one of the most fundamental processes, there are multiple scenarios where you’d count cells in Google Sheets:

  1. Count Empty Cells
  2. Count Non-Empty Cells
  3. Count Cells with Specific Values
  4. Count Cells Depending on Color

Google Sheets provides several different functions to support the counting of cells. And in this article, we will discuss each scenario and go through the simplest way to tackle them.

Let’s get started.

4 Scenarios to Count Cells in Google Sheets

1. Count Empty Cells in Google Sheets

The first scenario we will discuss is the count of empty or blank cells in Google Sheets.

Consider the following dataset:

sample worksheet to count cells in google sheets

We will count the blank cells in this Attendance dataset as Total Absents.

For this, Google Sheets provides us with the COUNTBLANK function.

COUNTBLANK(value1, [value2,...])

countblank function syntax in google sheets

The function essentially takes a range of cell references and returns the number of blank cells in them. It can also take multiple non-adjacent ranges as its argument.

To use it, simply pass the cell range as the argument of the COUNTBLANK function. In this case, these are all the values in the Meeting columns:


count blank cells in google sheets using the countblank function

But that’s not the end of it. If anything, Google Sheets provides its users with options and this scenario is no exception.

Alternative to COUNTBLANK, we can also use COUNTIF or the COUNTIFS functions to count blank cells in Google Sheets.





different formulas to count blank cells in google sheets

2. Count Non-Empty Cells in Google Sheets

In the next scenario, we want to count cells with a value in Google Sheets.

Luckily, we have a function that is the direct opposite of the COUNTBLANK function, the COUNTA function (Count All):

COUNTA(value1, [value2, ...])

counta function syntax in google sheets

The COUNTA formula to count all non-empty cells in Google Sheets is:


count all non-empty cells in google sheets using counta function

Once again, we have the alternative option to count with a condition with the COUNTIF function.


count all non-empty cells in google sheets using countif function

Note that the not-blank condition here is represented by a not symbol (<>). This is essentially the less than (<) and greater than (>) symbols side by side.

Learn More: Using COUNTIF to Count Non-Blank Cells in Google Sheets

3. Count Cells with Specific Numbers and Text

Counting cells can sometimes be required to be done using certain conditions, like specific texts or number values.

For these cases, the COUNTA function just does not cut it since it cannot take a condition or a criterion as an argument.

So instead, we will look to the function that can, the COUNTIF function.

COUNTIF(range, criterion)

countif function syntax in google sheets

Let’s have a look at a couple of examples.

I. Count Cells with Number Values in Google Sheets

From the following dataset, we want to count the number of cells that contain passing marks (Scores greater than 50).

sample dataset of scores

The COUNTIF formula is simple:


count cells according to number value in google sheets using countif function

II. Count Cells with Specific Text in Google Sheets

From the following dataset, we want to count all cells that contain the name “Mark”.

sample dataset of names

We will use the COUNTIF function, but there is a limitation:


the countif function only searches for specific values in the entire cell

The formula counts only the cell that solely has the value “Mark”. All the other instances are ignored. The COUNTIF function looks for direct matches in the cells.

But not to worry, we only have to make a minor change in the criterion field to count all occurrences. Simply include the asterisk (*) symbol on either side of the criterion:


count all occurrences of a text in google sheets using countif function

III. Count Cells For Specific Values with Multiple Criteria

Counting cells with multiple criteria is also possible with the COUNTIF function. For example, let’s say we want to count the occurrences of two names: Mark and Jones from the previous worksheet.

Since both names must be counted, we have to go for the OR condition. Or simply, we must add the COUNTIF results of both criteria:


count cells with multiple criteria in google sheets

Note: For the AND criteria of the count, you can use the COUNTIFS function.

Learn More: COUNTIF Multiple Criteria in Google Sheets (3 Ways)

4. Count Cells with Specific Colors in Google Sheets

Counting cells with specific color backgrounds is a unique yet common requirement in Google Sheets. Unfortunately, there are no built-in ways available in the application.

To remedy this, Google Sheets does allow its users to create their own functions with the help of Apps Script.

For this example, we will use the following worksheet:

sample worksheet with scores

This method does not affect the source dataset, so we can work with multiple conditions.

Step 1: Open the Apps Script window from the Extensions tab.

Extensions > Apps Script

navigating to the apps script option from the extensions tab in google sheets

Step 2: In the Apps Script window, enter the following code:

function CountColorCells(countRange,colorRef) {
  var activeRng = SpreadsheetApp.getActiveRange();
  var activeSheet = SpreadsheetApp.getActiveSheet();
  var activeFormula = activeRng.getFormula();
  var countRangeAddress = activeFormula.match(/\((.*)\,/).pop().trim();
  var bgs = activeSheet.getRange(countRangeAddress).getBackgrounds();
  var colorReference = activeFormula.match(/\,(.*)\)/).pop().trim();
  var backGround = activeSheet.getRange(colorReference).getBackground();
  var countCells = 0;
  for (var i = 0; i < bgs.length; i++)
    for (var k = 0; k < bgs[i].length; k++)
      if ( bgs[i][k] == backGround )
        countCells = countCells + 1;
  return countCells;

Make a note of the function name, CountColorCells. We will call on this function in our worksheet.

Step 3: Save the code by clicking on the Save icon up top. It may take a few seconds.

save the apps script code

After saving is complete, click on the Run button to activate the code.

run the code to activate it in the active google sheets worksheet

Google may seek permission to allow this Script to run. Allow it.

Step 4: Back in our worksheet the Script should be already running. All we have to do is call the function.

Do it in the same way you would a regular function. Then, insert the parameters for the range of cells and the background color reference, which in this case are the Passed or Failed cell background colors.

The formula for counting cells with Passed color is:


count colored cells with green background in google sheets using apps script

The formula for counting cells with Failed color is:


count colored cells with red background in google sheets using apps script

Note: It may take a couple of seconds to load the results. And do not worry if a redline appears under the formula. Reload the worksheet if problems persist.

How Does the CountColorCells Function Work?

The syntax for the function is simple:


The count_range field takes the range of cells from which the color will be checked and matched. In this case, these are the value cells of the Score column.

The background_color field acts as the color reference. If any cell in the count_range matches the background_color, the count will increase by 1.

Learn More: Count Cells with Color in Google Sheets (3 Easy Ways)

Final Words

That concludes the various scenarios and their respective ways to count cells in Google Sheets.

The most important takeaway from this is understanding the requirements of the count condition and employing a method accordingly. Which we hope we have clarified in this article.

Feel free to leave any queries or advice you might have in the comments section below.

Related Articles 

Mehrab Imtiaz

Mehrab Imtiaz

Mehrab Imtiaz is a Technical Writer for specializing in everything Google Sheets. Mehrab has always had a profound passion for writing and with his experience with MS Excel and Google Sheets throughout his career, it seems to be a great match when it comes to dishing out tips and tutorials for all to enjoy.

We will be happy to hear your thoughts

Leave a reply