Excel COUNT, COUNTA and COUNTBLANK Functions

Excel COUNT, COUNTA and COUNTBLANK Functions

If you just want a quick count of the number of items in a list or a range of cells you can simply select the range (with your mouse), and look at the Status Bar at the bottom right of your Excel window.

Excel Count Status Bar

This will count all cells that are NOT blank in your selected range.

Or for something more permanent you could use the COUNT, COUNTA or COUNTBLANK function depending on your needs.

They’re all very straight forward so let’s take a quick look at each one and I’ll show you some examples at the end.

Excel COUNT Function

COUNT Function Syntax

=COUNT(value1, [value2], …)

Where ‘value’ can be a single cell (you wouldn’t do this though as I hope you can already count to 1, if not my 2 year old can teach you, or for advanced counting my 5 year old says he can count to infinity!) or, more likely you will enter a range of cells in place of each ‘value’.

For example; you can count one range of cells:

=COUNT(A1:A500)

Or multiple ranges of non contiguous cells:

=COUNT(A1:A500,C1:C500,E1:G500)

There can be up to 30 ‘values’.

COUNT Function Rules

  • It only counts cells containing numbers
  • It ignores blank cells
  • It ignores cells containing anything but a number

Ok, that’s 3 ways to say the same thing but it leads me nicely onto the COUNTA function.

Excel COUNTA Function

Excel’s COUNTA function counts cells that are not empty.

That means it includes error values, like #VALUE!, numbers and blank spaces. I don’t mean blank cells, I mean cells with empty text like for example if you entered a space in a cell then COUNTA would count that cell.

COUNTA doesn’t count empty or blank cells. You need the COUNTBLANK function for that. More on COUNTBLANK below.

COUNTA Function Syntax

=COUNTA(value1, [value2], …)

Ditto COUNT function formula examples. That is; the ‘value’ in the COUNTA function syntax works the same as they do for the COUNT function.

Excel’s COUNTBLANK Function

COUNTBLANK Function Syntax

=COUNTBLANK(range)

You’ll notice that the syntax is ‘range’ and there’s only one of them. This is because unlike COUNT and COUNTA, the COUNTBLANK function cannot handle non-contiguous ranges.

The solution to this is to add COUNTBLANK functions together like this:

=COUNTBLANK(A4:B10)+COUNTBLANK(D4:D10)

COUNT, COUNTA and COUNTBLANK Examples

Excel COUNT, COUNTA & COUNTBLANK Function Examples

In rows 11-13 you can see the different results each formula returns depending on the Function used.

Download the workbook and reverse engineer the formulas.

Other ways to COUNT in Excel

  1. Use a Pivot Table
  2. Use an Excel Table and insert a COUNT total
  3. Use the SUBTOTAL Function

Or, if you want to count cells that match specific criteria then take a look at the COUNTIF and COUNTIFS functions.

FREE PDF Download
100 Excel Tips & Tricks

Excel Tips & Tricks E-Book
Just enter your details below
* indicates required

We respect your email privacy

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Current ye@r *

Comments

  1. Curt says

    I want to count non-contiguous cells i.e. C4, F4, G4, I4 >=1
    What I want to do is count non-contiguous cells whose value is greater or equal to one?

    What function would be appropriate for this solution?
    Count function requires a range i.e. =count(C4:I4)
    I am not sure if countif would work?

    Any help would be appreciated.

    • Catalin Bombea says

      Hi Curt,
      There is no built-in function to do this, you have to use:

      =SUM(COUNTIF(C4,">=1"),COUNTIF(F4:G4,">=1"))
      Or: COUNTIF(C4,">=1")+COUNTIF(F4:G4,">=1")+....

      Catalin

  2. Manjunath says

    I have an excel that consists A1 cell text A2 & A3 blank , A4 text A5 blank , A6 A7 A8 text & A9 A10 A11 is blank in the same format i have arround 20000 cell text & non text file & en number of files with me . i need a answer or formula count in the B1 as 3 , B2 & B3 blank , B4 as 2 ,B5 Blank, B6 as 6 , B7 B8 B8 B9 B10 B11 Blank.

  3. Martin Hughes says

    You haven’t mentioned whether cells with no data but with formulas or formatting or conditional formatting instructions are considered empty or not by these mechanisms.

    • says

      Hi Martin,

      Cells with formulas will be counted by COUNTA. COUNT will only count cells with formulas where the formula returns a number.

      Formatting won’t be counted by any of these formulas, nor will conditional formatting.

      I hope that helps.

      Kind regards,

      Mynda.

  4. Jacques says

    Hi. I want to know if it is possible to have a value in a cell and if you put another value in the same cell, it will add the two values.

    • Mynda Treacy says

      Hi Jacques,

      If you type another value in a cell already containing a value it will overwrite it. Unless you edit the cell and enter the additional value as a formula. e.g. if the cell contains 2, then you edit it (F2 to edit the cell) and modify it to =2+2 you will get 4, but that’s the only way (unless you use a more complex VBA solution).

      Kind regards,

      Mynda.