MoonPoint Support Logo


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

Advanced Search
Sun Mon Tue Wed Thu Fri Sat

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

1 applemaple
2  oak
3 plumbirch
4 birch

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.


  1. Excel -- Worksheet Functions -- Count Cells
    Last updated: November 11, 2006
    Contextures -- Microsoft Office Consulting
  2. Excel Developer Tip: Summing and Counting Using Multiple Criteria
    Excel Developer Tip Archives

[/os/windows/office/excel] permanent link

Once You Know, You Newegg AliExpress by

Shop Amazon Local - Subscribe to Deals in Your Neighborhood

Valid HTML 4.01 Transitional

Privacy Policy   Contact

Blosxom logo