Excel COUNT and COUNTA functions to count cells

This short tutorial explains the basics of the Excel COUNT and COUNTA functions and shows a few examples of using a count formula in Excel. You will also learn how to use the COUNTIF and COUNTIFS functions to count cells that meet one or more criteria.

As everyone knows, Excel is all about storing and crunching numbers. However, apart from calculating values, you may also need to count cells with values - with any value, or with specific value types. For example, you may want a quick count of all items in a list, or the total of inventory numbers in a selected range.

Microsoft Excel provides a couple of special functions for counting cells: COUNT and COUNTA. Both all very straightforward and easy-to-use. So let's take a quick look at these essential functions first, and then I will show you a few Excel formulas to count cells that meet certain condition(s), and clue you in on the quirks in counting some value types.

Excel COUNT function - count cells with numbers

You use the COUNT function in Excel to count the number of cells that contain numerical values.

The syntax of the Excel COUNT function is as follows:

COUNT(value1, [value2], …)

Where value1, value2, etc. are cell references or ranges within which you want to count cells with numbers.

In Excel 365 - 2007, the COUNT function accepts up to 255 arguments. In earlier Excel versions, you can supply up to 30 values.

For example, the following formula returns the total number of numeric cells in range A1:A100:

=COUNT(A1:A100)

Note. In the internal Excel system, dates are stored as serial numbers and therefore the Excel COUNT function counts dates and times as well.

Using COUNT function in Excel - things to remember

Below are the two simple rules by which the Excel COUNT function works.

  1. If an argument(s) of an Excel Count formula is a cell reference or range, only numbers, dates and times are counted. Blanks cells and cells containing anything but a numeric value are ignored.
  2. If you type values directly into the Excel COUNT arguments, the following values are counted: numbers, dates, times, Boolean values of TRUE and FALSE, and text representation of numbers (i.e. a number enclosed in quotation marks like "5").

For example, the following COUNT formula returns 4, because the following values are counted: 1, "2", 1/1/2016, and TRUE.

=COUNT(1, "apples", "2", 1/1/2016, TRUE)

Excel COUNT formula examples

And here are a few more examples of using the COUNT function in Excel on different values.

To count cells with numeric values in one range, use a simple count formula like

=COUNT(A2:A10)

The following screenshot demonstrates which types of data are counted and which are ignored:
Using the Excel COUNT function

To count several non-contiguous ranges, supply all of them to your Excel COUNT formula. For example, to count cells with numbers in columns B and D, you can use formula similar to this:

=COUNT(B2:B7, D2:D7)
Excel COUNT formula to count cells with numbers in several non-adjacent ranges.

Tips:

  • If you want to count numbers that meet certain criteria, use either the COUNTIF or COUNTIFS function.
  • If apart from numbers, you also want to count cells with text, logical values and errors, use the COUNTA function, which leads us right to the next section of this tutorial.

Excel COUNTA function - count non-blank cells

The COUNTA function in Excel counts cells containing any value, i.e. cells that are not empty.

The syntax of the Excel COUNTA function is akin to that of COUNT:

COUNTA(value1, [value2], …)

Where value1, value2, etc. are cell references or ranges where you want to count non-blank cells.

For example, to count cells with value in range A1:A100, use the following formula:

=COUNTA(A1:A100)

To count non-empty cells in several non-adjacent ranges, use a COUNTA formula similar to this:

=COUNTA(B2:B10, D2:D20, E2:F10)

As you can see, the ranges supplied to an Excel COUNTA formula do not necessarily need to be of the same size, i.e. each range may contain a different number of rows and columns.

Please keep in mind that Excel's COUNTA function counts cells containing any type of data, including:

  • Numbers
  • Dates / times
  • Text values
  • Boolean values of TRUE and FALSE
  • Error values like #VALUE or #N/A
  • Empty text strings ("")

In some cases, you may be perplexed by the COUNTA function's result because it differs from what you see with your own eyes. The point is that an Excel COUNTA formula may count cells that visually look empty, but technically they are not. For example, if you accidentally type a space in a cell, that cell will be counted. Or, if a cell contains some formula that returns an empty string, that cell will be counted as well.

In other words, the only cells that the COUNTA function does not count are absolutely empty cells.

The following screenshot demonstrates the difference between Excel COUNT and COUNTA functions:
Using the Excel COUNTA function to count cells with values

For more ways to count non-blank cells in Excel, check out this article.

Tip. If you just want a quick count of non-blank cells in a selected range, simply have a look at Status Bar at the bottom right corner of your Excel window:
A quick count of non-blank cells in a selected range

Other ways to count cells in Excel

Aside from COUNT and COUNTA, Microsoft Excel provide a few other functions to count cells. Below you will discuss 3 most common use cases.

Count cells that meet one condition (COUNTIF)

The COUNTIF function is purposed for counting cells that meet a certain criterion. Its syntax requires 2 arguments, which are self-explanatory:

COUNTIF(range, criteria)

In the first argument, you define a range where you want to count cells. And in the second parameter, you specify a condition that should be met.

For example, to count how many cells in range A2:A15 are "Apples", you use the following COUNTIF formula:

=COUNTIF(A2:A15, "apples")

Instead if typing a criterion directly in the formula, you can input a cell reference as demonstrated in the following screenshot:
Using the COUNTIF function in Excel

For more information, please see How to use COUNTIF in Excel.

Count cells that match several criteria (COUNTIFS)

The COUNTIFS function is similar to COUNTIF, but it allows specifying multiple ranges and multiple criteria. Its syntax is as follows:

COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)

The COUNTIFS function was introduced in Excel 2007 and is available in all later versions of Excel 2010 - 365.

For example, to count how many "Apples" (column A) have made $200 and more sales (column B), you use the following COUNTIFS formula:

=COUNTIFS(A2:A15,"apples", B2:B15,">=200")

To make your COUNTIFS formula more versatile, you can supply cell references as the criteria:
Using the Excel COUNTIFS function to count cells that meet several criteria

You will find plenty more formula examples here: Excel COUNTIFS function with multiple criteria.

Get a total of cells in a range

If you need to find out the total number of cells in a rectangular range, utilize the ROWS and COLUMNS functions, which return the number of rows and columns in an array, respectively:

=ROWS(range)*COLUMNS(range)

For example, to find out how many cells there are in a given range, say A1:D7, use the following formula:

=ROWS(A1:D7)*COLUMNS(A1:D7)
Count the number of cells in a range.

Well, this is how you use the Excel COUNT and COUNTA functions. Like I said, they are very straightforward and you are unlikely to run into any difficulty when using your count formula in Excel. If someone knows and is willing to share some interesting tips on to how to count cells in Excel, your comments will be greatly appreciated. I thank you for reading and hope to see you on our blog next week!

Table of contents

113 comments

  1. I need to your help with a formula im trying to build and its been days I have tried my ways but I haven’t reached anywhere

    1. I filter the data by Month as I have 6 months data.
    2. I filter it by unit as I have two unit
    3. I have 8 different columns with 8 metrics.
    4. 1st select the first data column and sort is desc to asc.
    5. I count the total day on that column.
    6. For eg if its 50 then I need the top 30. Mid 40 and bottom 30
    7. I multiply 50*30% give me m top30 and bottom 30 which is 15 each
    8. Remaining 20 as my mid40 percent
    9. I have to do this manually and assign the name on E Column for the first 15 entries as T30 then go on to other 20 and type M40 and remaining as B30.
    10. Now I have data in thousands and it’s a very tedious task to do this everytime manually.

    Is tehre a way to create a formula which takes count and cover the cells I need to my to mid and bottow.

    Month Center Unit Work per hour
    May-22 Mumbai BC 100 T30
    May-22 Mumbai BC 66 T30
    May-22 Mumbai BC 64 T30
    May-22 Mumbai BC 63 T30
    May-22 Mumbai BC 56 T30
    May-22 Mumbai BC 55 T30
    May-22 Mumbai BC 46 T30
    May-22 Mumbai BC 42 T30
    May-22 Mumbai BC 41 T30
    May-22 Mumbai BC 36 M40
    May-22 Mumbai BC 30 M40
    May-22 Mumbai BC 30 M40
    May-22 Mumbai BC 29 M40
    May-22 Mumbai BC 28 M40
    May-22 Mumbai BC 27 M40
    May-22 Mumbai BC 26 M40
    May-22 Mumbai BC 25 M40
    May-22 Mumbai BC 23 M40
    May-22 Mumbai BC 23 M40
    May-22 Mumbai BC 22 M40
    May-22 Mumbai BC 20 M40
    May-22 Mumbai BC 19 M40
    May-22 Mumbai BC 18 M40
    May-22 Mumbai BC 17 M40
    May-22 Mumbai BC 14 B30
    May-22 Mumbai BC 12 B30
    May-22 Mumbai BC 11 B30
    May-22 Mumbai BC 10 B30
    May-22 Mumbai BC 9 B30
    May-22 Mumbai BC 8 B30
    May-22 Mumbai BC 6 B30
    May-22 Mumbai BC 5 B30
    May-22 Mumbai BC 1 B30

  2. If I calculate rolls for example +70+50+70+70 of different yardings in one cell and i want to know the quantity of rolls (i.e., 4) in different cells. Then what formula should i apply?

  3. Problem: "Formula where result on Sheet 2 is generated from data on sheet 1"

    Hi,
    I want to write a formula like (len(AO2)) to calculate string characters in a cell in sheet2 having data from sheet1. How it will be done?

  4. How to combine COUNTIFS ADN COUNTA like this
    =COUNTIFS(C$9:C$707,B717,Q$9:Q$707,COUNTA(Q$9:Q$707))

  5. Hello!

    Seeking kind assistance I have Record Sales Monitoring, how to count TR NO. in Excel?

    Situation - I want to count Sales Transaction Number per Date?
    Date TR NO . Subtotal
    11/13/20 12344 95.50
    11/15/20 12345 428.00
    11/15/20 12345 398.00
    11/16/20 12350 179.00

  6. I'm trying to get the count of a named range using the value in a cell. I hope I can explain this well enough.

    I have the following dynamic range defined as:
    - all.space.name" =OFFSET('DB-Space'!$A$2, 0, 0, COUNTA('DB-Space'!$A:$A), 1)"
    - in cell d1 I have the text value of "all.space.name"
    - in cell d2 have the formula "=Counta(all.space.name) and it returns me the correct value

    Here is my issue: In cell D3, I want to get the counta of whatever I put in d1. I do not know how to write this, looked for hours but basically I want:
    =counta(value(d1))

    Thanks

    • Hello!
      Cell D1 contains text. Therefore the formula value(D1) is wrong. I don't understand what you want to find in D3. Number of values in D1 = 1

      • I need to count how many full 100s are in a cell e.g 598 would be 5

  7. Can the excel perform something like 'recount' function?
    For example:
    I would like to count the cells in batches.
    #Batch 1: 5 cells
    #Batch 2: 2 cells
    #Batch 3: 7 cells

    Can the count function run as follow?
    1
    2
    3
    4
    5
    1
    2
    1
    2
    3
    4
    5
    6
    7

    Your help is highly appreciated. Thank you.

    • Hello!
      I’m sorry but your task is not entirely clear to me. Could you please describe it in more detail? What result do you want to get? Write an example of the source data and the result you want to get.

  8. I currently am using the following formula to get a percentage of meetings attended in a year (12 total) and it works great for one variable but how do I get it to consider X and NM. I want it to recognize it as the same variable per say. Here is what I am currently using
    =COUNTIF($D$21:$O$21,”X”)/COUNTA($D$21:$O$21)
    Thank you in advance

    • Hello Becky!
      If you need to count the number of values under several conditions, use the COUNTIFS function. Read the detailed instructions at this link.
      I hope this will help, otherwise please do not hesitate to contact me anytime.

  9. I am trying to get only 3 digits number counts. I need how many 3 digits total count i need for below data. Please assist me to get ans.
    Table.
    453
    537
    661
    414
    3
    655
    123
    842
    727
    8
    787
    62
    Question : Count the No of numbers which contains 3 digits

  10. I want to use tickets sales formula in excel
    i.e. tickets selling from the ticket serial number 111000 to 111099.but my problem is when i entered the formula it shows me total sales of 99 tickets.but according to the serial number, total sales is 100 tickets.so,pls help me to create correct formula in excel.

  11. I want to count the cells as per the value is changed. For example I have four category in dropdown list. WIP, Delivered, IN queries and Queries answered. we put date when delivered and that gives me total date wise but when it in wip we dont put dates. I want count everyday how many have been change to WIP today

  12. hi, i would enquire what is the proper excel function to find out how many repeated numbers (for instance ABCD(1234), ABAC(1213), AABB(1122) or AAAB(1112)) in a spreadsheet with numerical data ranging from 0000 to 9999

  13. i want show separate cell please give me formula

  14. how to show weekly offs in attendance excel sheet

  15. Count the total number of children

  16. I am looking for something that looks like this:
    GetSetWithSum( count: 5, sum: 36)
    The result of such a formula would be something like this:

    5
    13
    9
    6
    3

  17. Hi. How do I add a string of text behind a formula? I am trying to count a column and add the words behind it for the report.

    Here is an example of what is not working:
    =counta((A3,a11) ": Total 2019 Submissions")

    Thank you.

  18. How can I do this -
    I am on Sheet2, looking for number of count of more than 80% from Sheet1. In Sheet1 there is coloum matching for "product" , "date" & "percentage". In the Sheet2 item matching from date & product. Can you please suggest me What formula I need to use to get the answer?
    thanks for your help in advance

  19. I am trying to add the total number of hours I have worked, out of a grand total (in this example 75 hours). Can I display the sum to read, for example, 48/75 to represent 48 hours of the total 75 hours needed?
    Thanks for your help.

  20. May be simple answer to this but still finding my feet in excel.
    I have a report that generates a list of dates and times like this (its all in the one cell. i.e. "16/10/2018 7:22:06 AM +10:00" is all in one cell):

    Last Update Date
    16/10/2018 7:22:06 AM +10:00
    15/10/2018 3:34:09 PM +08:00
    16/10/2018 7:23:08 AM +10:00
    16/10/2018 7:13:28 AM +10:00
    16/10/2018 7:10:23 AM +10:00
    15/10/2018 7:12:11 AM +10:00
    16/10/2018 7:18:01 AM +10:00
    12/10/2018 7:30:04 AM +10:00
    12/10/2018 7:23:00 AM +10:00
    11/10/2018 7:28:20 AM +10:00
    9/10/2018 7:07:33 AM +10:00
    8/10/2018 8:10:18 AM +10:00
    9/10/2018 7:40:25 AM +10:00

    the list is often hundreds long.

    What I need to do is generate separate counts for todays date, yesterdays date up to 5 days previous. The list is updated daily but always in the same format.

    The issue I'm running into is that all the data is the one cell and it wont recognise it as a date to count. I am unable to change how the report is generated :(

    Thanks for any help.

    • found a work around. I used the text to columns function in the Data tab. seems to work. If there is a better way would still appreciate the help.

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)