Google sheets count rows

The COUNT function in Google Sheets is one of the easiest to learn and extremely helpful to work with.

Even though it looks simple, it's capable of returning interesting and useful results, especially in combination with other Google functions. Let's get right into it.

What is COUNT and COUNTA in a Google spreadsheet?

The COUNT function in Google Sheets allows you to count the number of all cells with numbers within a specific data range. In other words, COUNT deals with numeric values or those that are stored as numbers in Google Sheets.

The syntax of Google Sheets COUNT and its arguments is as follows:

• Value1 (required) – stands for a value or a range to count within.
• Value2, value3, etc. (optional) – additional values that are going to be covered as well.

What can be used as an argument? The value itself, cell reference, range of cells, named range.

What values can you count? Numbers, dates, formulas, logical expressions (TRUE/FALSE).

If you change the content of the cell that falls into the counting range, the formula will automatically recalculate the result.

If multiple cells contain the same value, COUNT in Google Sheets will return the number of all its appearances in those cells.

To be more precise, the function counts the number of times numeric values appear within the range rather than checks if any of the values are unique.

Google Sheets COUNTA works in a similar way. Its syntax is also analogous to COUNT:

• Value (required) – the values we need to count.
• Value2, value3, etc. (optional) – additional values to use in counting.

What's the difference between COUNT and COUNTA? In the values they process.

COUNTA can count:

• Numbers
• Dates
• Formulas
• Logical expressions
• Errors, e.g. #DIV/0!
• Textual data
• Cells containing leading apostrophe (') even without any other data in them. This character is used at the beginning of the cell so that Google treats the string that follows as text.
• Cells that look empty but in fact contain an empty string (=" ")

As you can see, the main difference between the functions lies in the ability of COUNTA to process those values that Google Sheets service stores as text. Both functions ignore completely empty cells.

Have a look at the example below to see how the results of using COUNT and COUNTA differ depending on the values:

Since dates and time are stored and counted as numbers in Google Sheets, A4 and A5 were counted by both, COUNT and COUNTA.

A10 is completely empty, thus it was ignored by both functions.

Other cells were counted by the formula with COUNTA:

Both formulas with COUNT return the same result because A8:A12 range doesn't include numeric values.

A8 cell has a number stored as text which wasn't processed by Google Sheets COUNT.

The error message in A12 is entered as text and considered by COUNTA only.

How to use Google Sheets COUNT and COUNTA – examples included

Let's take a closer look at how the COUNT function is used in a Google spreadsheet and how it can benefit our work with tables.

Suppose we have a list of students' grades. Here are the ways COUNT can help:

As you can see, we have different formulas with COUNT in column C.

Since column A contains surnames, COUNT ignores that whole column. But what about cells B2, B6, B9, and B10? B2 has number formatted as text; B6 and B9 contain pure text; B10 is completely empty.

Another cell to bring your attention to is B7. It has the following formula in it:

Notice that the range starts from B2 and includes all other cells of this column. This is a very useful method when you often need to add new data to the column but want to avoid changing the range of the formula every time.

Now, how will Google Sheets COUNTA work with the same data?

As you can see and compare, the results differ. This function ignores only one cell – the completely empty B10. Thus, bear in mind that COUNTA includes textual values as well as numeric.

Here's another example of using COUNT to find an average sum spent on products:

The customers who haven't bought anything were omitted from the results.

One more peculiar thing regarding COUNT in Google Sheets concerns merged cells. There is a rule that COUNT and COUNTA follow to avoid double counting.

When the range for counting contains merged cells, they will be treated by both functions only if the upper-left cell falls within the range for counting.

For example, if we merge B6:C6 and B9:C9, the formula below will count 65, 55, 70, 55, 81, 88, 61, 92:

At the same time, the same formula with a slightly different range will work only with 80, 75, 69, 60, 50, 90:

The left parts of the merged cells are excluded from this range, therefore are not considered by COUNT.

COUNTA works in a similar way.

1. counts the following: 65, 55, 70, 55, 81, 88, 61, "Failed", 92. Just like with COUNT, empty B10 is ignored.
2. works with 80, 75, 69, 60, 50, 90. Empty C7 and C8, as in the case with COUNT, are ignored. C6 and C9 are omitted from the result since the range doesn't include the leftmost cells B6 and B9.

Count uniques in Google Sheets

If you'd rather count only unique values in the range, you'd better use the COUNTUNIQUE function. It requires literally one argument that can be repeated: a range or a value to process.

The formulas in spreadsheets will look as plain as this:

You can also enter multiple ranges and even records themselves directly to the formula:

Count with multiple criteria – COUNTIF in Google Sheets

In case the standard count is not enough and you need to count only specific values based on some conditions, there is another special function for that – COUNTIFS. All its arguments, the usage, and examples are covered in another special blog post.

I really hope that this article will assist your work with Google Sheets and that COUNT and COUNTA functions will serve you well.

You may also be interested in

When using Google Sheets, there may be times where you have cells with no data. In these cases, you may want to count the number of cells that are not blank. This is possible by using Google Sheet COUNT functions!

COUNTA is a function for counting cells that are not empty. Sometimes, however, the COUNTA function doesn't catch hidden special characters, so we can use SUMPRODUCT or COUNTIF to resolve this issue.

If you do not know how to use these functions, this tutorial is for you.

‍

COUNTA Method

In Google Sheets, the COUNTA function counts all the number of cells within a range that have values or a text string in it. It ignores cells that are empty while completing the count. 

As an example, here is data that has blank cells within a range:

To count the number of cells that are not blank, just follow these steps.

1. Select a blank cell and type the =COUNTA function including the range of cells that you want to count. For example, we used =COUNTA(A2:A11).
2. Just hit enter, and the COUNTA function will automatically count the cells that are not blank.
3. You now have the total number of cells that have values in it!

‍

SUMPRODUCT Method

COUNTA function is very useful in counting cells that are blank or have no data in it. However, there may be times that COUNTA counts cells that seem empty. This is because, technically, those cells are not blank but just have a white space appearing on it. It happens, for instance, when you use conditional formatting that makes it appear as blank or sneaking a function =”” in it!

To avoid this from happening, use SUMPRODUCT instead by following these steps:

1. Click on an empty cell and input the function =SUMPRODUCT(--(LEN(range)>0)) to count the cells that do not appear empty. In the process, the LEN function returns a value that is greater than zero while counting the number of characters that appear in the sheet.
2. Just click Enter and the number of the cells that have values in it will automatically appear.

‍

COUNTIF Method

If you have read the previous tutorials, Google Sheets hide some special characters from view because of their special functions: this include the apostrophe, that it uses to indicate that the number characters should be interpreted as a string (we used this to input phone numbers to Google Sheets). To solve this, we will learn how to use COUNTIF to count cells that are not blank and do not have special characters hidden.

As an example, let us look at this table:

How many cells have entries in the table? There are nine (9) entries in that table. However, if you apply COUNTA function to count the entries, the result would be thirteen (13):

The reason is that there are stray apostrophes and spaces in that table, and COUNTA counted them in. To exclude them from the count, we apply the COUNTIF function with a special attribute:

=COUNTIF(B3:E7,">0"&"*")

What happened? The “>0”&”*” serves as the condition to filter out cells that contain hidden characters, thus only the ones with letters and numbers are counted. 

Want to know more about Google Sheets?

Do you have more questions in mind about using Google Sheet? Check out our other spreadsheet resources and learn more functions and tricks that you can use when working!

ShareShareTweetEmailPrint

Prefer watching a video tutorial? Watch our video on how to count cells if not blank in Google Sheets below:

Sometimes you may have a dataset where some of the cells or rows are blank. In such a case, if you want to count cells that are not blank, then you need to rely on Google Sheets count functions.

In this tutorial, I will show you how to count cells if not blank in Google Sheets.

Count Cells If Not Blank Using COUNTA Function

COUNTA function in Google Sheets counts all the cells that have any value or text string in it, and ignores the blank cells.

For example, if you have a dataset as shown below and you want to count cells that are not blank, you can use the COUNTA function.

While the COUNTA function works well in most of the cases, it fails to give the right result when there is an empty string in the cell, or there is an apostrophe.

An empty string (“”) can be a result of a formula while apostrophe is often used by many people during data entry.

The empty string and apostrophe make the cell look blank, but are not ignored by the COUNTA function. So if you have a cell that has these, it will be counted by COUNTA function (as shown below):

Note that the cell A3 has an empty string (“”) and cell A7 has an apostrophe (‘). While these look empty, these cells are counted by the COUNTA function.

Hence, the result is 12, while it should be 10.

Count Cells If Not Blank Using COUNTIF function

Countif function in Google Sheets can be used to count the number of cells when a specified condition is met

To count all the cells that are not blank, you can use the following function:

Note that this formula ignores blank cells that contain an apostrophe or empty string.

This formula would also ignore Logical Values (TRUE/FALSE) and Errors.

You can also use the below SUMPRODUCT formula for counting non-blank cells in Google Sheets:

Sumit

Spreadsheet ExpertatProductivity Spot|Website|+ posts

Google Sheets and Microsoft Excel Expert.

Here's why in 3 easy steps

Step 1: Simple As Pie - Add Extra Column

The answer by eniacAvenger will yield the correct solution without worrying about edge cases as seems to arrive at the correct truthy/falsy value based on how we intuitively think of blank cells, either virgin blanks or created blanks.

So imagine we have this data and we want the Count of non-blanks in :

If we relied on Column C, we could get the count of values in B like this:

Step 2: Use to dynamically create Extra Column

However, consideRatio's comment is a valid one - if you need an extra column, you can often accomplish the same goal with an which can create a column in memory without eating up sheet space.

So if we want to create C dynamically, we can use an array formula like this:

If we simply put it in C2, it would create the vertical array with a single stroke of the pen:

Step 3: Count Values in Dynamic Column

But with that solved, we no longer need the column to merely display the values.

will resolve to the following range: .
just takes in any range and in this case can count the number of True values.

So we can wrap around the values produced by like this:

Further Reading

The other solutions in this thread are either overly complex, or fail in particular edge cases that I've enumerated in this test sheet:

Google Spreadsheet - CountA Test - Demo

For why works the wonky way it does, see my answer here

If you keep and manage records in Google Sheets, having a column with serial numbers is something you may need.

While you can easily create a column with serial numbers by manually entering these, it’s not an efficient way.

In this tutorial, I will show you a couple of ways to number rows in Google in such a way that it automatically add serial numbers.

This can easily be done using formulas and other simple techniques.

So let’s get started!

How to Number Rows in Google Sheets

In this tutorial,  I will show you four ways to add serial numbers in Google Sheets:

1. Using Fill Handle
2. Using the ROW function
3. Using the COUNTA Function
4. Adding 1 to Previous Serial number

The method you choose to numbers rows will depend on how your data is structured or how you want this to work.

For example, if there are blank rows in between and you only want to add a serial number to records that are not blank, you can not use all the methods (example covered later in this tutorial).

Number Rows Using Fill Handle

If you have a dataset without any blank rows, this is the easiest way to add serial numbers.

Suppose you have a dataset as shown below and you want to add serial numbers in column A.

Below are the steps to use the ‘Fill handle’ to fill down the serial numbers:

1. Insert a column to the left the Name column. To do this, right-click on any cell in column A and select ‘Insert Column’
2. [Optional] Give the new column a heading and format it like other columns
3. In cell A2, enter 1
4. In cell A3, enter 2
5. Select both the cells
6. Place the cursor at the bottom-right corner of the selection. The cursor would change into a plus icon (this is called the Fill Handle)
7. Double-click using the mouse (or left-click and drag till you want the numbers)

The above steps would give you serial numbers in the additional column you inserted. You can then format this column to make it look better.

The above steps work when you have a continuous set of the dataset (i.e., there are no blank rows). In case there is a blank record the dataset, double-clicking on the fill handle will only fill down the numbers till the filled record before the blank row.

Note: The result you get from this method is static. If you add more records to the dataset, you will have to the fill-down again do this to cover the new rows.

Using the ROW Function to Number Rows

ROW function is Google Sheets gives you the row number of the cell reference you give to it. And if you don’t give it any input argument, it will return the row number of the cell in which it is used.

You can use the ROW function (by adjusting it a little) to get the serial numbers in a column.

Suppose you have a dataset as shown below and you want to add serial numbers in column A.

Below are the steps to do this:

1. Insert a column to the left the Name column. To do this, right-click on any cell in column A and select ‘Insert Column’
2. [Optional] Give the new column a heading
3. In cell A2, enter the formula: =ROW()–1
4. Copy and paste for all the cells where you want the serial number.

The above formula uses the ROW function to get the current row number and then subtracts 1 from it as we are starting from the second row onwards. In case you’re starting from the first row, you can simply use the ROW function.

In case you only want to show the serial number for the records that are filled, you can use the below formula:

You can use this formula on an expanded number of cells in the column (even if there are no records in it for now). As soon as you add a new record, the number would automatically appear.

The above uses an IF function to check whether there is any text in the adjacent cell or not. In case there is no text, it returns a blank, else it returns the row number.

The good thing about using the ROW function is that in case you delete a row from this dataset, the function would adjust to give you the correct row number.

Using the COUNTA Function

Another way you can number rows in Google Sheets is by using the COUNTA function.

The COUNTA function will count the number of non-empty cells adjacent cells and return a serial number only when the row is filled (unlike the ROW function method, which would still give a serial number if the row is empty).

Suppose you have a dataset as shown below and you only want to assign a row number (serial number) to a row if that row is filled.

The below formula can do this:

In the above formula, the IF function checks whether the adjacent cell is blank or not (using the ISBLANK function). If it’s blank, the IF formula would return a blank and if it isn’t, it will count all the filled rows in the specified range and give that number.

Note that I am using $B$2:B2 as the range where I have locked the first B2 (by having dollar sign before the column alphabet and row number) but not the second one. This allows me to expand the range when I copy it to other cells. For example, if I copy this to cell B5, it will expand and the range would become $B$2:B5

Adding 1 to Previous Serial number

This is a quick and simple way to add serial numbers to a column. It’s best suited when you have no blank rows in the dataset.

Suppose you have a dataset as shown below where you want to insert serial numbers in column A.

Below are the steps to do this:

1. Enter 1 in cell A2
2. In cell A3, enter the formula =A2+1
3. Copy and paste this formula for all the cells in the column where you want the numbers

The above formula simply adds 1 to the number in the cell above it.

While this is a simple and straightforward method, there are a few drawbacks you must know before using this method:

• This would assign a number irrespective of whether there are blank rows or not.
• In case you delete a row, all the values below it would return a reference error (#REF!)
• When you insert a new row, it will not update the row number automatically. You will have to copy and paste the formula to all the cells in this new row and all the cells below it.

So these are four methods you can use to numbers rows in Google Sheets. You can choose any of these methods based on how your data is structured. In case you have blank rows in your data set, it’s best to use the COUNTA method.

Hope you found this tutorial useful!

You may also like the following Google Sheets tutorials:

To count the number of cells in a range there is no any specific function in Google Sheets. But to count the number of values in a range there are many Count functions. Then how to count the total number of cells in a selected range in Google Doc Sheets?

To count the number of cells in a range, you can use the functions ROWS and COLUMNS independently or combined.

The ROWS function returns the number of rows in a range.

For example;

Result: 10

Similarly, you can use the COLUMNS function to return the number of columns in a range.

Result: 3

You can combine both these functions in Google Docs Sheets to find the total number of cells in a range. See that with one example below.

The Formula to Count The Number of Cells in a Range in Google Sheets

In the below example, I am using the above same range.

To find the total number of cells in a range, you can use the below generic formula in Spreadsheets.

Generic Formula

Which is equal to;

Refer to the image.

I can give you a few more examples. Let me count another range for total cells.

Range to Count: A1: Z1

Formula:

Result: 26

Range to Count: A1: A10

Formula:

Result: 10

Important Note: When you have only one row or one column in your range you can simply deviate from the above generic formula. But the above generic formula would also work.

For example, to count the total number of cells in a column, simply use the COLUMNS function as below.

Formula:

Result: 26

To count the total number of cells in a row, simply use the ROWS function as below.

Formula:

Result: 10

You have now learned how to count the number of cells in a range in Google Sheets. Now see one real-life example.

Real-life Example to Count Number of Cells in a Range in Google Doc Sheets

I find the above formula useful in conditional formatting. Of course, there may be tons of other uses.

See how to highlight a cell if all the cells in a range have values.

Test Range: A1: C10

Highlight Rule: Highlight cell F4 to Green, if all the cells in the range A1: C10 contains TRUE.

Note: I have inserted tick boxes in all the cells in A1: C10. If ticked, the value in that cell will be TRUE, else FALSE.

Must Check: 10 Best Tick Box Tips and Tricks in Google Sheets.

That means I want to highlight the cell F4 when all the tick boxes are ticked.

Formula:

To apply this formula do as follows.

1. Open the Conditional format rules panel on the sidebar. To do that go to the menu Format > Conditional formatting…

2. Type F4 in the Apply to range.

3. Select Custom formulais under the Format cells if… and type the above formula.

4. Select the color and click Done.

That’s all about how to count the number of cells in a range in Google Sheets. Hope you have enjoyed the stay!