Count cells that contain any text

Question

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?

score 301 · Accepted Answer · edited Jul 15 '18 at 17:48

301

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

edited Jul 15 '18 at 17:48

Andy

17,423
9
52
69

answered Oct 05 '12 at 13:21

SwDevMan81

48,814
22
151
184

6

No "otherwise" about it. `CountA` is the way to do it. – Stephen Jan 30 '15 at 14:38
9

countA is not the way if you count generated cells since it counts also "" as 1. – Eugene Kaurov Jan 20 '16 at 10:40
6

=COUNTIF(A1:A10,"<>") – Eugene Kaurov Jan 20 '16 at 10:40
10

=COUNTA(A1:A10)-COUNTBLANK(A1:A10) – Eugene Kaurov Jan 20 '16 at 10:43
3

It's `=COUNTIF(A1:A10;"<>")` note the *semicolon* instead of the comma. – paulvs Jun 28 '16 at 14:22
1

@paulvs - Looks like google sheets will change the semicolon to a comma – SwDevMan81 Jun 28 '16 at 14:27
1

@eugene-kaurov: Ive posted an answer [here](http://stackoverflow.com/questions/24081707/google-spreadsheet-count-rows-with-not-empty-value/43422433#43422433) which excludes generated blank cells – spacepickle Apr 15 '17 at 05:33
4

@paulvs, it depends on the locale. – Qwertiy Jul 05 '17 at 21:37

score 34 · Answer 2 · answered Dec 25 '13 at 18:56

34

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.

answered Dec 25 '13 at 18:56

cloacker

351
3
2

In my tests, `COUNTA` does not count blank cells (i.e. has nothing). So you are incorrect in saying so. – hazrpg Aug 05 '15 at 11:24
An empty string of length 0 is not necessarily the same as a blank cell. A blank cell has a value of NULL (or similar, depending of system); a cell containing a string of length 0 has contents. – Canned Man Nov 07 '16 at 12:55
@hazrpg, but it counts cells with `=""` – Qwertiy Jul 05 '17 at 21:36

score 7 · Answer 3 · answered Feb 04 '19 at 12:14

7

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,"?*")

answered Feb 04 '19 at 12:14

Marc van Maastricht

71
1
2

score 3 · Answer 4 · answered Aug 28 '18 at 11:46

Sample file

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 goolge-spreadhseet: =ARRAYFORMULA(SUM(IFERROR(IF(data="",0,1),1))). For excel ={SUM(IFERROR(IF(data="",0,1),1))} should work (press Ctrl+Shift+Enter in the formula).

score 2 · Answer 5 · answered Jul 05 '17 at 21:44

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.

score 1 · Answer 6 · edited Apr 08 '20 at 10:14

1

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)

edited Apr 08 '20 at 10:14

Maxim Mazurok

3,856
2
22
37

answered Apr 08 '20 at 08:02

Rishi Mittal

23
7

score 1 · Answer 7 · answered Sep 25 '21 at 00:39

This works.

=ArrayFormula(SUM(IF(ISTEXT(put-your-range-of-text-mixed-with-anything-here),1,0),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.
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.
Sum(range) then totals the 1's (Trues/Cells that are entirely text) and ignores the 0's (Falses/Cells not entirely text).
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.

Mr Shane · Answer 8 · 2022-01-06T06:06:46.647

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)

Count cells that contain any text

8 Answers8

Linked