Either the
counta or countif functions can
be used to count the number of cells that are not blank in
a spreadsheet.
E.g., suppose you have the following information in a spreadsheet
| A | B | |
| 1 | apple | maple |
| 2 | oak | |
| 3 | plum | birch |
| 4 | birch | |
| 5 | apricot |
The function counta(A1:A5) would count those cells that
aren't empty in cells A1 to A5 yielding 3.
The function countif(B1:B5,"*") would also count those
cells that contain text in cells B1 to B5, yielding 4
as well.
Note: counta will count cells that have formulas
in them even though those formulas may amount to nothing appearing
in the cell, i.e. they evaluate to "".
If you wanted to count the number of cells that contained specific
text, e.g. birch, you could use
countif(B1:B5,"birch"), which would yield 2.
The count is case insensitive, i.e., if one of the cells contained "BIRCH",
instead of "birch", the count would still be equal to two.
If you wanted to count the number of cells that contained "birch" in
column B and "plum" in column A, then countif won't work, since
you can only specify one criteria with it. You could use sum
instead. E.g. sum((B1:B5="birch")*(A1:A5="plum")), which
yields 1. The * in this case "ands" the two
conditions. Note: You have to hit the Ctrl, Shift, and Enter keys
simultaneously when you've typed the formula in order to enter it. If you
don't, you will see a #value appear in the cell. This type of
formula is considered to be an "array" formula or CSE formula, since it
requires the Ctrl-Shift-Enter keys to be hit to enter it. When it is entered,
you will see {} appear around the formula.
References:
