May 23 2007

COUNTA and COUNTBLANK Formulas in Excel

When using Excel, there will be many instances where you will want to count a particular range of cells. Excel has numerous ways built in already to count that range for you. One way we already explored is through the CountIf Formula, and also the Count Formula. Excel also features the CountA and CountBlank Formulas to make your counting even easier. Let’s go ahead and take a look at each of the formulas:

COUNTA(value1,value2,…)

COUNTBLANK(RANGE)

Both COUNTA and COUNTBLANK can accept individual objects or a Range of Cells. The difference between the two is what they are made to count. COUNTA counts the number of cells that are not empty and returns the number of cells containing some form of data. COUNTBLANK, on the other hand, will count the number of empty cells in the range and return the total (the opposite of COUNTA). Next let’s take a look at some examples.


EXAMPLES

CountA CountBlank Example

Example 1: =counta(A2:A10) will return 8 since there are 8 cells in the range that are not blank.

Example 2: = countblank(C2:C10) will return 4 since there are 4 blanks within that range of cells. Counta and Countblank basically perform opposite actions: Counta counts non blanks and CountBlank only counts the blanks.

Example 3: = count(B2:B10) will return 7 since there are 7 cells in the range that contain numbers. In this example Counta and Count will return the same result since every cell in the range that isn’t blank contains a number. In the next example you will see what happens when count is used on non-number cells.

Example 4: =count(A2:A10) will return 0 since non of the cells in the range contain numbers.

In all the example, we used a very small sample set to explore the uses of each of the count functions. When you begin using these functions for larger sets of data you will immediately realize their benefit and potential. Please contact me if you have any further questions on these formulas or would like further explanation.

** Excel Hints provides Excel Tips and Excel Help for All Levels of Experience.

More Excel Tips

Did you find this post helpful? Try signing up for our free daily tips.

Enter your email address: 

          

2 Comments on this post

Trackbacks

  1. Mike said:

    I noticed that counta() does NOT do the opposite of countblank(). Allow me to explain:

    Cell A1 contains a formula:
    if(someothercell=1;contentsofsomecell;””)
    Since “someothercell” contains 2, the cell is blank
    COUNTBLANK(A1) will return 1
    COUNTA, on the other hand, will notice there is something in the cell, namely a formula.
    COUNTA(A1) will therefore ALSO return 1
    This is a major gotcha.

    January 15th, 2008 at 5:25 am
  2. Rossi said:

    For Open Office Calc, use $:Addresses and References, Absolute and RelativeRelative AddressingThe cell in column A, row 1 is adesersdd as A1. You can address a range of adjacent cells by first entering the coordinates of the upper left cell of the area, then a colon followed by the coordinates of the lower right cell. For example, the square formed by the first four cells in the upper left corner is adesersdd as A1:B2.By addressing an area in this way, you are making a relative reference to A1:B2. Relative here means that the reference to this area will be adjusted automatically when you copy the formulas.Absolute AddressingAbsolute references are the opposite of relative addressing. A dollar sign is placed before each letter and number in an absolute reference, for example, $A$1:$B$2.OpenOffice.org can convert the current reference, in which the cursor is positioned in the input line, from relative to absolute and vice versa by pressing Shift +F4. If you start with a relative address such as A1, the first time you press this key combination, both row and column are set to absolute references ($A$1). The second time, only the row (A$1), and the third time, only the column ($A1). If you press the key combination once more, both column and row references are switched back to relative (A1)OpenOffice.org Calc shows the references to a formula. If, for example you click the formula =SUM(A1:C5;D15:D24) in a cell, the two referenced areas in the sheet will be highlighted in color. For example, the formula component A1:C5 may be in blue and the cell range in question bordered in the same shade of blue. The next formula component D15:D24 can be marked in red in the same way.

    December 10th, 2015 at 7:59 pm

LEAVE A COMMENT

Subscribe Form

Subscribe to Blog

Sign Up for Excel Hints

Free updates with Excel Hints, Tips & Tricks

Categories

Archives