• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Excel COUNT, COUNTA and COUNTBLANK Functions

You are here: Home / Excel Formulas / Excel COUNT, COUNTA and COUNTBLANK Functions
Excel COUNT, COUNTA and COUNTBLANK Functions
July 6, 2011 by Mynda Treacy

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.

Enter your email address below to download the sample workbook.



By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

Download the workbook and reverse engineer the formulas. Note: This is a .xlsx file. Please ensure your browser doesn't change the file extension on download.

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.

Excel COUNT, COUNTA and COUNTBLANK Functions

More Statistical Posts

Excel MIN MAX SMALL and LARGE Functions

Excel MIN MAX SMALL and LARGE Functions

Microsoft Excel Standard Deviation Functions

Microsoft Excel Standard Deviation Functions

Excel COUNTIF and COUNTIFS Formulas Explained

How to write Excel COUNTIF and COUNTIFS formulas. Including workbook download.

More Excel Formulas Posts

top excel functions for data analysts

Top Excel Functions for Data Analysts

Must know Excel Functions for Data Analysts and what functions you don’t have to waste time learning and why.
excel advanced formula environment

Excel Advanced Formula Environment

Excel Advanced Formula Environment is a long awaited, new improved way to write, name and store Excel formulas.
Pro Excel Formula Writing Tips

Pro Excel Formula Writing Tips

Must know Excel formula writing tips, tricks and tools to make you an Excel formula ninja, including a new formula editor.
excel shaping arrays

New Array Shaping Excel Functions

The Excel Shaping Array Functions makes it easier than ever to reshape arrays and ranges using these purpose built functions
excel nested if functions what not to do

Excel IF Formulas and What Not To Do

Excel IF formulas can get out of hand when you nest too many IFs. Not only do they become unwieldy they’re difficult for anyone to understand
excel image function

Excel IMAGE Function

The Excel IMAGE Function enables you to embed images in a cell using a formula. It supports BMP, JPG/JPEG, GIF, TIFF, PNG, ICO, and WEBP files

Excel VSTACK and HSTACK Functions

New Excel VSTACK and HSTACK functions makes combining arrays of cells easy and with some clever tricks we can extend their capabilities.
identify overlapping dates and times in excel

Identify overlapping dates and times in Excel

How to identify overlapping dates and times in Excel with a formula that checks a range of cells. Works with Dates and Times.
New Excel Text Functions

TEXTSPLIT, TEXTBEFORE and TEXTAFTER Functions

TEXTAFTER, TEXTBEFORE and TEXTSPLIT are exciting new Excel Text functions. They’re fairly self-explanatory, however TEXTSPLIT has some cool features.

Top 10 Intermediate Excel Functions

Take your Excel skills to the next level with this top 10 intermediate Excel functions. These are must know functions for all Excel users.
Category: Excel FormulasTag: statistical
Previous Post:Save Time with an Excel WorkspaceSave Time with an Excel Workspace
Next Post:Excel SUBSTITUTE Function TrickExcel SUBSTITUTE Function Trick

Reader Interactions

Comments

  1. Steve Reed

    March 16, 2022 at 12:22 am

    HI, I’ve hit a brick wall. I’m using a filter to create a list of customers and then want to count them. Unfortunately, the CountA function returns 1 when the is empty, i.e. #CALC! error.

    I tried using ISERROR to do a test but that returns whole list of numbers which in turn causes a SPILL error.

    I’ve spent 2 days on this now and got nowhere.

    Any help appreciated

    Reply
    • Mynda Treacy

      March 16, 2022 at 8:51 am

      Hi Steve, please post your question on our Excel forum where you can also upload a sample file and we can help you further.

      Reply
    • Philip Treacy

      March 16, 2022 at 8:55 am

      Hi Steve,

      The cell must really be empty to not be counted by COUNTA. If the cell contains, for example, an empty string “”, the cell isn’t really empty and COUNTA will count it.

      Are you using a filter on a table or the FILTER function?

      Hard to say anymore without seeing your file. Please start a topic on our forum and attach your file.

      Regards

      Phil

      Reply
  2. Sue Smith

    March 28, 2019 at 12:22 am

    How would I add a text string behind a counta formula? The example below gives me an error. Thank you!

    =counta((A3,a11) “: Total 2019 Submissions”) 

    Reply
    • Catalin Bombea

      March 28, 2019 at 1:23 pm

      Hi Sue,
      Try:
      =counta(A3,a11) & “: Total 2019 Submissions”

      Reply
  3. Greg

    October 15, 2016 at 12:48 am

    I am trying to put a count in to a 30 sheet workbook to track if a time is put in a cell, how would I go about writing the the formal for that ? I think it may be =COUNT(‘1:31’!I12) I12 being the cell that I need the count of how many time a time is put in there.

    Reply
    • Mynda Treacy

      October 16, 2016 at 9:44 pm

      Hi Greg,

      If you enter =COUNT( then click in the cell on the first sheet, hold down SHIFT and click on the cell in the last sheet, Excel will insert the cell reference for you.

      Mynda

      Reply
  4. Curt

    July 13, 2014 at 11:25 am

    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.

    Reply
    • Catalin Bombea

      July 13, 2014 at 1:48 pm

      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

      Reply
  5. viola

    May 14, 2014 at 5:27 pm

    greate, e mbrekullueshme from albania

    Reply
    • Philip Treacy

      May 15, 2014 at 12:25 pm

      Thanks Viola

      Reply
  6. Manjunath

    April 11, 2013 at 6:31 am

    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.

    Reply
    • Carlo Estopia

      April 11, 2013 at 1:35 pm

      Hi Manjunath,

      Please clarify this further via Help Desk.

      Cheers,

      CarloE

      Reply
  7. Martin Hughes

    February 18, 2013 at 8:54 pm

    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.

    Reply
    • Mynda Treacy

      February 18, 2013 at 9:55 pm

      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.

      Reply
  8. Jacques

    August 20, 2012 at 6:29 pm

    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.

    Reply
    • Mynda Treacy

      August 20, 2012 at 6:51 pm

      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.

      Reply

Leave a Reply Cancel reply

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

Current ye@r *

Leave this field empty

Sidebar

More results...

Shopping Cart

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now
  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
 
  • About My Online Training Hub
  • Contact
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Copyright © 2023 · My Online Training Hub · All Rights Reserved

Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.

Download A Free Copy of 100 Excel Tips & Tricks

excel tips and tricks ebook

We respect your privacy. We won’t spam you.

x