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?
It's work for me:
Count of all non-empty cells from F2 to the end of the column
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 exampleHere
ArrayFormula(IF(B3:B29&C3:C29&D3:D29&E3:E29="";0;1))
returns a column of0
(if the row is empty) and1
(else).Another one is given in consideRatio's answer.
In Google Sheets, to count the number of rows which contain at least one non-empty cell within a two-dimensional range:
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/
I just used
=COUNTIF(Range, "<>")
and it counted non-empty cells for me.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
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
:If we relied on Column C, we could get the count of values in B like this:
Step 2: Use
FormulaArray
to dynamically create Extra ColumnHowever, 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:
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.
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 byArrayFormula
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
CountA
works the wonky way it does, see my answer hereGiven the range
A:A
, Id suggest: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
""
but excluding truly empty cells""
but excluding truly blank cellsThis 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""