CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
Robbit
Robbit

Reputation: 1577

Count rows with not empty value

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?

Upvotes: 142

Views: 225205

Answers (13)

nomnom
nomnom

Reputation: 994

It works for me:

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

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

Upvotes: 10

Will Rice
Will Rice

Reputation: 21

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/

Upvotes: 2

Kpym
Kpym

Reputation: 4023

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;

Upvotes: 2

Kpym
Kpym

Reputation: 4023

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.

Upvotes: 4

Pablo
Pablo

Reputation: 537

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.

Upvotes: 0

KyleMit
KyleMit

Reputation: 29829

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)

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 CountA works the wonky way it does, see my answer here

Upvotes: 42

Delta_zulu
Delta_zulu

Reputation: 1610

=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.

Upvotes: 136

user300905
user300905

Reputation: 57

A simpler solution that works for me:

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

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

Upvotes: 4

spacepickle
spacepickle

Reputation: 2778

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 ""

Upvotes: 4

David Veszelovszki
David Veszelovszki

Reputation: 2772

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

Upvotes: 19

Mike
Mike

Reputation: 2201

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

Upvotes: 216

consideRatio
consideRatio

Reputation: 1151

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.

Upvotes: 9

eniacAvenger
eniacAvenger

Reputation: 875

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.

Upvotes: -17

Related Questions