Shop Amazon Warehouse Deals - Deep Discounts on Open-box and Used Products

February
Sun Mon Tue Wed Thu Fri Sat

19

2008
Months
Feb

### Tue, Feb 19, 2008 11:27 pm

Counting Non-Blank Cells in a Spreadsheet and Using Multiple Criteria for Counting

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:

Shop Amazon Local - Subscribe to Deals in Your Neighborhood