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?

Answer 1

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.