Count rows with not empty value

Question

In a Google Spreadsheet: How can I count the rows of a given area that have a value? All hints about this I found up to now lead to formulas that do count the rows which have a not empty content (including formula), but a cell with

=IF(1=2;"";"")  // Shows an empty cell

is counted as well.

What is the solution to this simple task?

Wouldn't CountA work for you? Also see this SO thread: stackoverflow.com/questions/12519073/… — Ozair Kafray
– Ozair Kafray, Commented Oct 5, 2015 at 4:00
Possible Duplicate of google docs count cells that contain any text — KyleMit
– KyleMit ♦, Commented Apr 10, 2017 at 14:21

gmuraleekrishna · Accepted Answer · 2016-03-21 17:28:33Z

218

I just used =COUNTIF(Range, "<>") and it counted non-empty cells for me.

edited Mar 21, 2016 at 17:28

gmuraleekrishna

3,4311 gold badge29 silver badges46 bronze badges

answered Mar 21, 2016 at 16:48

Mike

2,2211 gold badge11 silver badges2 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

David Veszelovszki Over a year ago

Unfortunately, this formula also counts cells with formulas of blank results.

shawnrad Over a year ago

I had the same issue as @DávidVeszelovszki [formulas were counted]. I solved with =COUNTIF(Range,"?*")

AnnanFay Over a year ago

The question is asking how to count the rows in a range. You are counting the cells in a range?

johntellsall · Accepted Answer · 2017-05-26 20:42:00Z

138

=counta(range)

counta: "Returns a count of the number of values in a dataset"

Note: CountA considers "" to be a value. Only cells that are blank (press delete in a cell to blank it) are not counted.

Google support: https://support.google.com/docs/answer/3093991
countblank: "Returns the number of empty cells in a given range"

Note: CountBlank considers both blank cells (press delete to blank a cell) and cells that have a formula that returns "" to be empty cells.

Google Support: https://support.google.com/docs/answer/3093403

If you have a range that includes formulae that result in "", then you can modify your formula from

=counta(range)

to:

=Counta(range) - Countblank(range)

EDIT: the function is countblank, not countblanks, the latter will give an error.

edited May 26, 2017 at 20:42

johntellsall

15.2k4 gold badges48 silver badges40 bronze badges

answered May 18, 2016 at 22:17

Delta_zulu

1,6401 gold badge11 silver badges9 bronze badges

3 Comments

David Veszelovszki Over a year ago

Unfortunately, this does not work for ranges that include both virgin blanks and calculated blanks. (It subtracts the non-counted virgin blanks from COUNTA's value.)

Delta_zulu Over a year ago

Thanks for the sweet downvotes, the OP specifically mentioned that the reason he has blanks is because of formula like =IF(1=2;"";""), and I specifically mentioned: "If you have a range that includes formulae that result in ""..."

AnnanFay Over a year ago

The question is asking how to count the rows in a range. You are counting the cells in a range?

KyleMit · Accepted Answer · 2017-08-25 20:26:22Z

42

Here's what I believe is the best solution so far:

=CountIf(ArrayFormula(range<>""),TRUE)

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 =A1<>"" 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 B2:B6:

|   |      A      |   B   |    C    |
|---|-------------|-------|---------|
| 1 | Description | Value | B1<>""  |
| 2 | Text        | H     | TRUE    |
| 3 | Number      | 1     | TRUE    |
| 4 | IF -> ""    |       | FALSE   |
| 5 | IF -> Text  | h     | TRUE    |
| 6 | Blank       |       | FALSE   |

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

=COUNTIF(C2:C6,True)

Step 2: Use `FormulaArray` 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 ArrayFormula 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:

=ArrayFormula(B2:B6<>"")

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

|   |      A      |   B   |    C                     |
|---|-------------|-------|--------------------------|
| 1 | Description | Value | =ArrayFormula(B2:B6<>"") |
| 2 | Text        | H     | TRUE                     |
| 3 | Number      | 1     | TRUE                     |
| 4 | IF -> ""    |       | FALSE                    |
| 5 | IF -> Text  | h     | TRUE                     |
| 6 | Blank       |       | FALSE                    |

Step 3: Count Values in Dynamic Column

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

ArrayFormula will resolve to the following range: {True,True,False,True,False}.
CountIf just takes in any range and in this case can count the number of True values.

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

=CountIf(ArrayFormula(B2:B6<>""),TRUE)

Google Spreadsheet - CountA Test - Demo

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

edited Aug 25, 2017 at 20:26

answered Apr 12, 2017 at 1:10

KyleMit♦

31.4k74 gold badges517 silver badges712 bronze badges

4 Comments

spacepickle Over a year ago

This answer is the only complete solution: It successfully counts only cells with text (including space), numbers, and TRUE/FALSE values whilst excluding generated blanks, truly empty cells, and errors (#N/A, #REF!, #VALUE, #NAME?) . It will also return 0 where there are no values

twindham Over a year ago

I got a little confused reading this, and accidentally discovered that this formula works too... =CountIf(ArrayFormula(B2:B6<>""),TRUE) ... Please, don't get me wrong, this answer is the best one, and should be upvoted more.

KyleMit Over a year ago

@twindham, agreed, that in terms of building the blocks of this answer that it's probably easier to keep each logical component intact and reorder ArrayFormula(...) to return the array of values inside of CountIf as you suggested. I've updated the answer and the Spreadsheet demo. They'll both do the same thing, but cleanliness is next to goodliness.

AnnanFay Over a year ago

The question is asking how to count the rows in a range. You are counting the non-blank cells in a single column? Can your approach work over multiple columns?

David Veszelovszki · Accepted Answer · 2016-05-24 17:55:13Z

19

For me, none of the answers worked for ranges that include both virgin cells and cells that are empty based on a formula (e.g. =IF(1=2;"";""))

What solved it for me is this:

=COUNTA(FILTER(range, range <> ""))

answered May 24, 2016 at 17:55

David Veszelovszki

2,7922 gold badges25 silver badges24 bronze badges

4 Comments

user1561325 Over a year ago

Great solution in case if a columnb contains formuals which returns "" value! Thanks a lot

KyleMit Over a year ago

Unfortunately, it looks like if Filter() doesn't find any elements, it will return #N/A which COUNTA() treats as an element and so will always return 1, even if the count should be zero. Example in Google Sheets

spacepickle Over a year ago

@KyleMit You can get rid of the zero count issue by adding a constant value to the range and subtracting 1 from the count with =COUNTA(filter({1;range},{1;range}<>"")) - 1

AnnanFay Over a year ago

The question is asking how to count the rows in a range. You are counting the cells in a range. This code breaks if you try to apply it to a range of rows.

nomnom · Accepted Answer · 2021-01-02 15:53:46Z

12

It works for me:

=SUMPRODUCT(NOT(ISBLANK(F2:F)))

Count of all non-empty cells from F2 to the end of the column

edited Jan 2, 2021 at 15:53

answered Jul 29, 2019 at 14:41

nomnom

1,01411 silver badges22 bronze badges

Comments

consideRatio · Accepted Answer · 2015-01-09 23:52:03Z

9

Solved using a solution i found googling by Yogi Anand: https://productforums.google.com/d/msg/docs/3qsR2m-1Xx8/sSU6Z6NYLOcJ

The example below counts the number of non-empty rows in the range A3:C, remember to update both ranges in the formula with your range of interest.

=ArrayFormula(SUM(SIGN(MMULT(LEN(A3:C), TRANSPOSE(SIGN(COLUMN(A3:C)))))))

Also make sure to avoid circular dependencies, it will happen if you for example count the number of non-empty rows in A:C and place this formula in the A or C column.

answered Jan 9, 2015 at 23:52

consideRatio

1,17115 silver badges19 bronze badges

2 Comments

David Veszelovszki Over a year ago

Found a much cleaner solution, I posted it as a new answer.

Kpym Over a year ago

The only solution that answer the question to count the number of rows with non empty cell inside, and not to count the number of non empty cells in a range.

spacepickle · Accepted Answer · 2017-04-15 05:11:07Z

4

Given the range A:A, Id suggest:

=COUNTA(A:A)-(COUNTIF(A:A,"*")-COUNTIF(A:A,"?*"))

The problem is COUNTA over-counts by exactly the number of cells with zero length strings "".

The solution is to find a count of exactly these cells. This can be found by looking for all text cells and subtracting all text cells with at least one character

COUNTA(A:A): cells with value, including "" but excluding truly empty cells
COUNTIF(A:A,"*"): cells recognized as text, including "" but excluding truly blank cells
COUNTIF(A:A,"?*"): cells recognized as text with at least one character

This means that the value COUNTIF(A:A,"*")-COUNTIF(A:A,"?*") should be the number of text cells minus the number of text cells that have at least one character i.e. the count of cells containing exactly ""

answered Apr 15, 2017 at 5:11

spacepickle

2,79819 silver badges21 bronze badges

3 Comments

spacepickle Over a year ago

Using @KyleMit demo sheet, i found this formula still over counts because it will include any cells with errors

Cheeso Over a year ago

There's a difference between a cell that contains a zero length string and a cell that contains nothing at all? This is what makes people distrust computers. . . Do you see why we can't have nice things?

AnnanFay Over a year ago

The question is asking how to count the rows in a range. You are counting the cells in a range?

user300905 · Accepted Answer · 2017-05-23 13:04:20Z

4

A simpler solution that works for me:

=COUNTIFS(A:A;"<>"&"")

It counts both numbers, strings, dates, etc that are not empty

answered May 23, 2017 at 13:04

user300905

571 silver badge2 bronze badges

2 Comments

Tom Over a year ago

could be simplified in a form like =COUNTIF(A:A,"<>")

AnnanFay Over a year ago

The question is asking how to count the rows in a range. You are counting the cells in a range?

Kpym · Accepted Answer · 2018-11-07 09:48:27Z

4

As far as I can see, most of the solutions here count the number of non empty cells, and not the number of rows with non empty cell inside.

One possible solution for the range B3:E29 is for example

=SUM(ArrayFormula(IF(B3:B29&C3:C29&D3:D29&E3:E29="";0;1)))

Here ArrayFormula(IF(B3:B29&C3:C29&D3:D29&E3:E29="";0;1)) returns a column of 0 (if the row is empty) and 1 (else).

Another one is given in consideRatio's answer.

answered Nov 7, 2018 at 9:48

Kpym

4,0931 gold badge26 silver badges18 bronze badges

Comments

Kpym · Accepted Answer · 2018-11-07 11:19:41Z

2

You can define a custom function using Apps Script (Tools > Script editor) called for example numNonEmptyRows :

function numNonEmptyRows(range) {
  Logger.log("inside");
  Logger.log(range);
  if (range && range.constructor === Array) {
    return range.map(function(a){return a.join('')}).filter(Boolean).length
  }
  else {
    return range ? 1 : 0;
  }
}

And then use it in a cell like this =numNonEmptyRows(A23:C25) to count the number of non empty rows in the range A23:C25;

answered Nov 7, 2018 at 11:19

Kpym

4,0931 gold badge26 silver badges18 bronze badges

Comments

Will Rice · Accepted Answer · 2019-06-10 23:36:34Z

2

In Google Sheets, to count the number of rows which contain at least one non-empty cell within a two-dimensional range:

=ARRAYFORMULA(
  SUM(
    N(
      MMULT(
        N(A1:C5<>""),
        TRANSPOSE(COLUMN(A1:C5)^0)
      )
      >0
    )
  )
)

Where A1:C5 is the range you're checking for non-empty rows.

The formula comes from, and is explained in the following article from EXCELXOR - https://excelxor.com/2015/03/30/counting-rows-where-at-least-one-condition-is-met/

answered Jun 10, 2019 at 23:36

Will Rice

213 bronze badges

Comments

Pablo · Accepted Answer · 2018-09-18 16:22:25Z

0

A very flexible way to do that kind of things is using ARRAYFORMULA.

As an example imagine you want to count non empty strings (text fields) you can use this code:

=ARRAYFORMULA(SUM(IF(Len(B3:B14)>0, 1, 0)))

What happens here is that "ArrayFormula" let you operate over a set of values. Using the SUM function you indicates "ArrayFormula" to sum any value of the set. The "If" clause is only used to check "empty" or "not empty", 1 for not empty and 0 otherwise. "Len" returns the length of the different text fields, there is where you define the set (range) you want to check. Finally "ArrayFormula" will sum 1 for each field inside the set(range) in which "len" returns more than 0.

If you want to check any other condition, just modify the first argument of the IF clause.

edited Sep 18, 2018 at 16:22

answered Sep 18, 2018 at 16:11

Pablo

5582 gold badges8 silver badges18 bronze badges

Comments

Rhence · Accepted Answer · 2025-02-04 03:11:38Z

0

The following code works for me:

=COUNTIF([range],">0"&"*")

answered Feb 4 at 3:11

Rhence

11 bronze badge

1 Comment

Starship Feb 11 at 2:22

Don't add code-only answers. Explain what your code does, why it works, and how it helps to solve the problem.

eniacAvenger · Accepted Answer · 2014-09-22 22:03:19Z

-17

Make another column that determines if the referenced cell is blank using the function "CountBlank". Then use count on the values created in the new "CountBlank" column.

answered Sep 22, 2014 at 22:03

eniacAvenger

88312 silver badges18 bronze badges

4 Comments

Robbit Over a year ago

I consider this as a clear statement that there is no other solution to this. Thank you very much.

consideRatio Over a year ago

When you need to add another column, you can almost always solve it by using some extra formulas like containing some concatinate/split/join etc, ill get back to you if i solve it.

consideRatio Over a year ago

For example, the following line makes one single row out of a 2d range of cells, that can often solve a lot of problems: =split(ArrayFormula(concatenate(C3:O4&";")),";") ---- ill keep working on solving the issue

consideRatio Over a year ago

I posted an answer that solves the goal without relying on separate columns

Collectives™ on Stack Overflow

Count rows with not empty value

14 Answers 14

3 Comments

3 Comments

Here's what I believe is the best solution so far:

Here's why in 3 easy steps

Step 1: Simple As Pie - Add Extra Column

Step 2: Use `FormulaArray` to dynamically create Extra Column

Step 3: Count Values in Dynamic Column

Further Reading

Google Spreadsheet - CountA Test - Demo

4 Comments

4 Comments

Comments

2 Comments

3 Comments

2 Comments

Comments

Comments

Comments

Comments

1 Comment

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

14 Answers 14

3 Comments

3 Comments

Here's what I believe is the best solution so far:

Here's why in 3 easy steps

Step 1: Simple As Pie - Add Extra Column

Step 2: Use FormulaArray to dynamically create Extra Column

Step 3: Count Values in Dynamic Column

Further Reading

4 Comments

4 Comments

Comments

2 Comments

3 Comments

2 Comments

Comments

Comments

Comments

Comments

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related

Step 2: Use `FormulaArray` to dynamically create Extra Column