Google sheets count rows

Google sheets count rows DEFAULT

How to Count Cells If Not Blank in Google Sheets

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.Count Cells If Not Blank in Google Sheets - COUNTA

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):

Count Cells Non Blank in Google Sheets - empty string

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:

=COUNTIF($A$1:$A$13,"?*")+COUNT($A$1:$A$13)

Count Cells If Not Blank in Google Sheets - Countif

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:

=SUMPRODUCT(LEN(A1:A13)>0))

 

Sumit

Spreadsheet ExpertatProductivity Spot|Website|+ posts

Google Sheets and Microsoft Excel Expert.

Sours: https://productivityspot.com/count-non-blank-cells-google-sheets/

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.

Example to Count Number of Cells in a Range in Google Sheets

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.

highlight a cell if all the cells in a range 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!

Prashanth

Sours: https://infoinspired.com/google-docs/spreadsheet/count-the-number-of-cells-in-a-range/
  1. Realistic female mannequin
  2. Province park condos for sale
  3. Olight baldr rl holster
  4. Cost of land in mexico

Google Sheets COUNT and COUNTA functions with formula examples

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:

COUNT(value1, [value2,…])
  • 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.

Tip. To count unique values in the range, use the COUNTUNIQUE function instead.

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

COUNTA(value1, [value2,…])
  • 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:
COUNT and COUNTA in comparison

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.

Tip. To set more precise calculation conditions, I recommend you to use COUNTIF function instead.

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:
Variations of COUNT for students' grades

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?

COUNTA for students' grades

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:

COUNT to find the average

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.

Note. The functions take into account only the leftmost cell of the merged range.

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:

COUNT, COUNTA and merged cells

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.

=COUNTUNIQUE(value1, [value2, ...])

The formulas in spreadsheets will look as plain as this:
Count unique records in Google Sheets.
You can also enter multiple ranges and even records themselves directly to the formula:
Scan multiple ranges/values for uniques.

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

Sours: https://www.ablebits.com/office-addins-blog/2017/11/16/count-counta-google-sheets/
COUNT, COUNTIF, COUNTIFS functions in Google Sheets

How to Number Rows in Google Sheets (Add Serial Numbers)

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.

Dataset where the row numbers serial number needs to be added

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’Insert a column to the left
  2. [Optional] Give the new column a heading and format it like other columns
  3. In cell A2, enter 1Enter 2 in the second cell right below where you have 1
  4. In cell A3, enter 2Enter 2 in the second cell right below where you have 1
  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)Place the cursor at the bottom right of the selection
  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.

Data where rows have been numbered

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:

=IF(B2<>"",ROW()-1,"")

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.

Dataset where there are empty rows

The below formula can do this:

=IF(ISBLANK(B2),"",COUNTA($B$2:B2))

Numbering rows with formula when there are blank cells

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.

Number rows in Excel with formula to add previous cell

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:

Sours: https://spreadsheetpoint.com/number-rows-in-google-sheets/

Count google rows sheets

How to Count Rows Between Two Values in Google Sheets

Knowing how to count rows between two values in Google Sheets is useful if you want to find out how far one value is from another in terms of the number of rows.

Meaning, regardless of whether there are values in between or not, this article would teach you how to generate or write a formula to count the rows between two specific values.

There is no specific function that we can use to be able to achieve this task. What we will use, instead, is a combination of function and formula. Please see the rules below before we continue further:

  • The MATCH function is the main function of this process. It returns the relative position of an item within the specified range. Feel free to visit this MATCH function article for a more detailed explanation and samples.
  • If multiple instances of search value appear in the column that we’re searching into, the MATCH function returns the row number of the first instance. This will possibly cause issues in the process of counting the rows between cell values.

Let’s take an example.

Please take a look at the table below:

Jenny, an inventory clerk to a mini fruit store, is asked to prepare a report that shows the total number of days each fruit item has been sold for quarter three.

While there may be other ways to do it, she used the very simple trick she knows. Jenny thought that if she’d count the rows between the fruit items, then that could be the values she’s looking for.

Let’s have another real-life sample!

Teacher Brent is keeping his records organized in Google Sheets. One of his records is scoresheets for every quiz he provides to his students. Please see one of the samples below:

To be able to always make him aware of the number of students he has, Brent tends to create an identifier on each of his scoresheets. Check how he was able to identify the number of students above. Note that this number auto-adjusts as soon as he adds or removes students’ names. How convenient is that!

Brent simply counts the rows between the text ‘Name’ and ‘Total Scores’. Easy right?

Watch out for a more advanced tutorial and examples on how you can count rows between two values in the coming weeks. Be sure to subscribe to be notified. 

Awesome! Let’s begin getting to know more about the steps and processes in counting rows between two values in Google Sheets.

 

The Anatomy of the MATCH Function used for counting row between values

So the syntax (the way we write) the MATCH function is as follows:

=MATCH(search_key, range, [search_type])

Let’s dissect this thing and understand what each of these terms means:

  • = the equal sign is just how we start any function in Google Sheets. It is how Google Sheets understand that we are asking it to either do a computation or use a function.
  • MATCH() this is our main function.
  • search_key is the value we will search for within the range.
  • range the one-dimensional array to be searched for the search_key.
  • search_type is the manner in which to search [optional].

Please visit this article for more information about the MATCH function.

 

A Real Example of Counting Rows Between Two Values

Take a look at our fruit inventory example below to see how the MATCH function is used in obtaining the number of rows between two specific values.

In the example above, our goal is to find the distance between the word ‘Apples’ and ‘Oranges’ in terms of rows. 

Take note of how many instances MATCH function were used in the formula. The first one is used to be able to get the relative position of the word ‘Orange’, which is 9 (Row 9). 

The second match function was used to get the relative position of the word ‘Apples’, which is 3 (Row 3). These values, 9 and 3, are vital in getting the number of rows between the words ‘Apples’ and ‘Oranges’. How?

It’s like 9-3-1, which is 5. So basically, the formula should be:

First MATCH function Second MATCH function – 1

Amazing right? Who would have thought that a combination of arithmetic operation and two MATCH functions would come in handy? 🙂

Please know that the 1 on the last part of the formula is constant on this kind of scenario. However, it could change depending on your goal. 

See our second example below:

What if we want to find out the combined number of days of Apples and Oranges?

Since we will be calculating the distance between the text value ‘Apples’ and ‘Pears’ now, the first argument of both our MATCH functions will be changed to ‘Pears’ and ‘Apples’ for the first function and second function, respectively.

Notice that we are now subtracting 2, instead of 1, from the difference of two MATCH functions. It is because of the extra rows that shouldn’t be included in the count:

That’s how we are able to come up with the value 13, which is the total number of days the Apples and Oranges were sold.

 

You may make a copy of the spreadsheet using the link I have attached below.

How to Count Rows Between Two Values in Google Sheets

  1. Click on any cell to make it the active cell. For this guide, I will be selecting E3, where I want to show my result.

 

  1. Next, type the equal sign ‘=‘ to begin the function and then followed by the name of the function, which is our ‘match‘ (or ‘MATCH‘, not case sensitive).

 

  1. Type open parenthesis ‘(‘ or simply hit Tab keyto let you use that function.

 

  1. Now the fun part! Let’s give our first MATCH function its first argument, which is the text value to look for. You may pass constant data by typing the exact text value. For this example, I’ll be using the cell address of the text value ‘Total Scores’. Type in B11.

 

  1. To let the Google sheet know that we’re done typing our first argument, we should now type in the delimiter or the character that separates each argument on a function. In this case, type comma,’ followed by the next argument, which is the range to be searched for the text value ‘Total Scores’. Type in B:B.

 

  1. Type in comma (,) followed by the last argument, which is 0. 0 since we are looking for the exact match in column B of the word ‘Total Scores’.

 

  1. Finally, end the first function by typing in close parenthesis )’.

 

  1. To let the Google Sheet know that we are subtracting a value from our first MATCH function, type in hyphen’ followed by our second MATCH function.

 

  1. Pass the first argument of our second MATCH function, which is the cell address of the text value ‘Name’. Type in B2 followed by the delimiter comma ,’.

 

  1. Provide the second argument, which is the column B or simply type in B:B, followed by comma,’ and the third argument, which is 0.

 

  1. Close the second MATCH function by typing in the closing parenthesis )’.

 

  1. Now, subtract 1 by continuing typing in hyphen’ and 1.

  1. Finally, hit Enter or your Tab key. Cell E3 should now show you the distance between the text values ‘Name’ and ‘Total Scores’. For Brent, on his scoresheet, it should identify how many students he has.

 

That’s pretty much it. You can now count rows between two values in Google Sheets together with the other numerous Google Sheets formulas to create even more powerful formulas that can make your life much easier.

Get emails from us about Google Sheets.

Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'll love what we are working on! Readers receive ✨ early access ✨ to new content.

 

Roderick Timbal

Five years of my work experience involved Google Sheets, which I've worked on with fascination. Logic and numbers give me the thrill, call it my game! And just to add, I used to train college students with Excel topics way back then.

Sours: https://www.sheetaki.com/count-rows-between-two-values-in-google-sheets/
Google Sheets - Count Cells Containing Specific Text

This may be a trivial request, but it seems like there should be a way to do this. In Microsoft Excel, as one highlights a collection of rows, there is pop-up of both the row and column counts. Here is a screen shot of what one sees.


In Google Sheets, this same pop-up does not occur. Is there a way to natively count the number of rows when highlighting the rows, especially when filtering the data, within Google Sheets without the need for any specific formulas or coding?


7 Replies

· · ·

jayadi

Pimiento

OP

HI Google sheet Guy, 

Yes we can get the results what you required.

First highlight collection of rows and columns and then click on INSERT Tab you will see the result. 

example: 12 Rows above and 5 Colum Left.

Will be same as above pic 12R X 5C.

Hope My answer helps you to get the results.

2

· · ·

starg33ker

Ghost Chili

OP

You'll also see the results on the far bottom right

0

· · ·

4UByMe

Jalapeno

OP

starg33ker wrote:

You'll also see the results on the far bottom right

While there is some information available, the row count isn't part of that. I even clicked the up/down arrows to change what information is visible in the lower right corner when a series of rows or columns is selected. If you are able to display simple row/column information, please share.

0

· · ·

4UByMe

Jalapeno

OP

jayadi wrote:

HI Google sheet Guy, 

Yes we can get the results what you required.

First highlight collection of rows and columns and then click on INSERT Tab you will see the result. 

example: 12 Rows above and 5 Colum Left.

Will be same as above pic 12R X 5C.

Hope My answer helps you to get the results.

Thanks. While not exactly what I was looking for, this gives me the information I need. 

0

· · ·

starg33ker

Ghost Chili

OP

Can you provide a screenshot? I'm unable to replicate.


1

· · ·

4UByMe

Jalapeno

OP

starg33ker wrote:

Can you provide a screenshot? I'm unable to replicate.

No idea what I was smoking. I tried this on two different computers using two different browsers. From what I can tell, R/C isn't explicitly referenced. Instead, if there is any text in the selected cells, the lower right shows "Count: xx." If the selected cells only include numbers, the name "Count" is referenced in the lower right, but there are up/dwn arrows to get other information such as: Sum, Max, Min, Avg.

Thank you both for your help in making me see the obvious. Gonna be a long week...

1

· · ·

spicehead-6y5vg

Pimiento

OP

Well, I got even more interesting question.  How do I get the count of randomly selected rows.  Example: CMD Click on entire rows: 2,4,6,8,15,30.  I would like to get the count of selected rows which should equal to 6.  Can't seem to figure this one out.

0

This topic has been locked by an administrator and is no longer open for commenting.

To continue this discussion, please ask a new question.

Sours: https://community.spiceworks.com/topic/2117811-count-when-highlighting-rows-in-google-sheet

You will also be interested:

How to Count Non-Empty Cells in Google Sheets

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:

Google Sheet table with Customer Name and Total Ordered Amount. Several blank rows are interspersed throughout

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!
Same table as above, but now with a circled cell counting the non-blank cells in Column A and the function circled

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!

Same table as above except with new cell in Column B counting non-blanks (but having the wrong amount due to the COUNTA function)

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.
Same table as above, but with a correct non-blank count for Column B using the SUMPRODUCT function

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:

Google Sheets table with only 9 entries entered

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):

Google Sheets table with only 9 entries entered, but COUNTA showing 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!

Sours: https://www.lido.app/tutorials/count-non-empty-cells-in-google-sheets


158 159 160 161 162