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?
8 Answers
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
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 .
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,"?*")
Note:
- Tried to find the formula for counting non-blank cells (
=""is a blank cell) without a need to usedatatwice. 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).
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.
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)
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.
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)
