CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
steak
steak

Reputation: 2227

How to count number of non-blank cells (cells that contain text)?

I want to count the cells that contain anything within a range. Any cell that contain text, or numbers or something else should do a plus one in my result-cell.

I found this function,

countif(range; criteria)

but this doesn't work for me, because I don't know what to type in the criteria. How can I do this?

Upvotes: 214

Views: 342780

Answers (8)

Mr Shane
Mr Shane

Reputation: 636

to count any cells that has anything in it (including any text or number values, and also formulas that have text or numeric or blank results), then use:

=COUNTIF(A1:A10, "<>")

or

=COUNTA(A1:A10)

to count only cells that have a value that is NOT blank (including text or numeric values, or formulas that have numeric or text values, but NOT including formulas that have blank results) ie: cell value is longer than zero characters, then use:

=SUMPRODUCT(--(LEN(A1:A10)>0))

or

=COUNTA(FILTER(A1:A10, A1:A10 <> ""))

to count only cells that have a text (alphanumeric) value, then use:

=COUNTIF(A1:A10,"?*")

to count only cells that have a numeric value, then use:

=COUNT(A1:A10)

to count any cells that are blank (including cells that have no value, and cells where the formula result is blank), then use:

=COUNTBLANK(A1:A10)

Upvotes: 0

Weekend Warrior
Weekend Warrior

Reputation: 11

This works.

=ArrayFormula(SUM(IF(ISTEXT(put-your-range-of-text-mixed-with-anything-here),1,0),1))

  1. IsText(range) looks at your data and returns True for every cell that is text and false for every one that is not. I think these are returned into a data table/array. See step 4.
  2. If(IsText(range),1,0) takes the True/False values from the array/table returned by IsText in step 1, and translates the Trues into 1's and the Falses into 0's, as true integers, not strings.
  3. Sum(range) then totals the 1's (Trues/Cells that are entirely text) and ignores the 0's (Falses/Cells not entirely text).
  4. For some reason, ArrayFormula is needed to return the sum of all cells back into one cell, rather than returning the sum of all cells into a table of equal size. Idk. Would appreciate it if someone knowledgable could please add to this.

Bon chance.

Upvotes: 1

Rishi Mittal
Rishi Mittal

Reputation: 23

COUNTIF function can count cell which specific condition where as COUNTA will count all cell which contain any value

Example: Function in A7: =COUNTA(A1:A6)

Range:

A1| a

A2| b

A3| banana

A4| 42

A5|

A6|

A7| 4 (result)

Upvotes: 1

Marc van Maastricht
Marc van Maastricht

Reputation: 101

The criterium should be "?*" and not "<>" because the latter will also count formulas that contain empty results, like ""

So the simplest formula would be

=COUNTIF(Range,"?*")

Upvotes: 10

Max Makhrov
Max Makhrov

Reputation: 18697

Sample file

enter image description here

Note:

  • Tried to find the formula for counting non-blank cells (="" is a blank cell) without a need to use data twice. The solution for : =ARRAYFORMULA(SUM(IFERROR(IF(data="",0,1),1))). For ={SUM(IFERROR(IF(data="",0,1),1))} should work (press Ctrl+Shift+Enter in the formula).

Upvotes: 3

SwDevMan81
SwDevMan81

Reputation: 49978

You can pass "<>" (including the quotes) as the parameter for criteria. This basically says, as long as its not empty/blank, count it. I believe this is what you want.

=COUNTIF(A1:A10, "<>")

Otherwise you can use CountA as Scott suggests

Upvotes: 312

Qwertiy
Qwertiy

Reputation: 21380

If you have cells with something like ="" and don't want to count them, you have to subtract number of empty cells from total number of cell by formula like

=row(G101)-row(G4)+1-countblank(G4:G101)

In case of 2-dimensional array it would be

=(row(G101)-row(A4)+1)*(column(G101)-column(A4)+1)-countblank(A4:G101)

Tested at google docs.

Upvotes: 2

cloacker
cloacker

Reputation: 361

COUNTIF function will only count cells that contain numbers in your specified range.

COUNTA(range) will count all values in the list of arguments. Text entries and numbers are counted, even when they contain an empty string of length 0.

Example: Function in A7 =COUNTA(A1:A6)

Range:

A1 a

A2 b

A3 banana

A4 42

A5

A6

A7 4 -> result

Google spreadsheet function list contains a list of all available functions for future reference https://support.google.com/drive/table/25273?hl=en.

Upvotes: 35

Related Questions