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!

51 comments

  1. 1) How can i count how many customers picked a call agent calls
    2) How much was spent on each call

  2. I want to know how to count male and female having specific degree. for eg. If I am preparing table for interview of teachers and applicants are male and female with qulifications like ph. d. m.sc. m.a. net or set etc. the how to calculate how many male are Phd or net or only m.sc. if we have written all their qualification in one row only.

  3. I'm wanting to add data after a counta formula. How/Can I do that? Example: Column A ...my formula is =counta(a2:a50) and that total is 49 ....I'm wanting to add the words Employees Shift A after it in the same cell. Can't figure out how to do that.

    Any help would be greatly appreciated.

    Thanks!!

  4. 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?

  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 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

    1. 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.

  7. 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.

    1. 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.

  8. L4/5
    L4/5
    L3/4, L4/5
    L4/5
    L3/4, L4/5
    L3/4, L4/5, L5/S1
    L3/4, L4/5
    L4/5, L5/S1
    L4/5, L5/S1
    L5/S1
    L3/4, L4/5
    L5/S1
    L3/4, L4/5
    L4/5
    L2/3, L3/4
    L3/4, L4/5
    L2/3, L3/4, L4/5
    L4/5, L5/S1
    L3/4, L4/5, L5/S1
    L5/S1
    L3/4, L4/5

    I have data that looks like the above (a range of A1:A238)

    I want to count for example how many times "L1/2" appears in the range A1:A238. When I use the function =COUNTIF(A1:A238, "L1/2") it gives me the answer two, but I think it is only counting cells that contain only "L1/2" and not perhaps those that contain "L1/2, L2/3, L3/4".

    I would like to count every time L1/2 is listed in the range from A1:A238

    Thanks in advance!

    1. Rusty:
      As you have discovered COUNTIF will not work in this situation. Instead, you'll need to use SUMPRODUCT, ISNUMBER and FIND or SEARCH.
      SEARCH is not case sensitive, FIND is case sensitive and both will return a position which ISNUMBER returns as a hit and then SUMPRODUCT returns as the sum of the hits.
      The formula will look like this:
      =SUMPRODUCT(--(ISNUMBER(SEARCH("L1/2",A1:A238))))

  9. I have 3 columns. Column 1 is a list of questions. Column 2 is Yes/No/ N/A Dropdown. Column 3 is Score. If column 2 is "Yes", put a 1 in the score column. If column 2 is a "No", put a "0". If column 2 is "N/A", don't populate the cell with anything. What I want to do is have Excel count all cells in column 3 that have a value (1 or 0), then calculate the number of 1's against the full count and place the percentage at the top of the Score Column (Cell F3)

    1. Just an FYI... the first part is already done (Yes/No/N/A translating to a value of 1 or 0). I'm trying to figure out how to calculate the score (by percentage) and place it at the top of the Score column

      1. Dan:
        I guess the easiest way to do what you want is to use the functions COUNTIF and COUNTA.
        Where the scores are in C51:C57 the formula is:
        =COUNTIF(C51:C57,1)
        then to get the total number of non-empty cells the formula is: =COUNTA(C51:C57)
        So if you enter these formulae in D4 and D5 respectively
        the percentage is derived by D4/D5 and format the cell as percentage.

  10. Please help me with this problem. I would like to highlight all rows where the Name and Date column values match AND the Procedures listed on the same date definitely has 921 listed and also has either 992, 993, 994 or a combination of these procedures listed on the same date (Each procedure is listed once in the row, so if they have 921 and 922 on one date that will be two rows). So for each matching name and date there will be at least two rows highlighted. Thank you so much!

  11. I have a spreadsheet where patients are admitted at a certain time then discharged a certain time. I need a formula to count the number of patients that are in ER during a 1 hour time.
    See table below.
    In Column B I have the Admission time and in Column C I have the Discharge time.
    If I have 33 pts admitted at different hours throughout the day, I would like a formula to count the number of patients that are in ER at 5:00 then at 6:00 then at 7:00 and so on.
    Below is the table I have.
    With manual counting I have 2 pts in ER at 5:00, 4 patients in ER at 6:00 and so one. Is there a formula for this.

    A B C
    Patient Admission Time Discharge Time
    1 5:14:00 AM 7:21:00 AM
    2 5:29:00 AM 6:33:00 AM
    3 6:01:00 AM 9:06:00 AM
    4 6:25:00 AM 7:10:00 AM
    5 6:42:00 AM 9:45:00 AM
    6 6:46:00 AM 8:15:00 AM
    7 8:13:00 AM 8:18:00 AM
    8 8:32:00 AM 11:15:00 AM
    9 8:42:00 AM 11:00:00 AM
    10 8:48:00 AM 11:26:00 AM
    11 8:54:00 AM 10:42:00 AM
    12 9:00:00 AM 11:45:00 AM
    13 9:05:00 AM 9:40:00 AM
    14 9:52:00 AM 1:40:00 PM
    15 10:12:00 AM 1:00:00 PM
    16 10:33:00 AM 12:11:00 PM
    17 11:02:00 AM 11:21:00 AM
    18 11:29:00 AM 3:18:00 PM
    19 12:28:00 PM 3:16:00 PM
    20 12:50:00 PM 2:00:00 PM
    21 1:44:00 PM 3:37:00 PM
    22 3:47:00 PM 4:49:00 PM
    23 3:58:00 PM 5:10:00 PM
    24 5:05:00 PM 5:16:00 PM
    25 6:40:00 PM 7:45:00 PM
    26 6:55:00 PM 10:11:00 PM
    27 7:17:00 PM 9:55:00 PM
    28 8:09:00 PM 9:09:00 PM
    29 8:15:00 PM 11:00:00 PM
    30 9:28:00 PM 10:43:00 PM
    31 10:26:00 PM 11:26:00 PM
    32 10:49:00 PM 11:00:00 PM
    33 11:11:00 PM 11:37:00 PM

    1. Marlene:
      If you want to count the number of patients admitted during a particular hour this is how I would do it.
      Where the data is in A1:C34 including column labels;
      To the right of this data add three columns. The first in D1 is labeled Admin Time, the second in E1 is labeled Sum of Admin Time and the third is in F1 and is labeled Time Wanted. Clearly you can label them as you see fit.
      In D2 enter =MROUND(B2,"1:00") this will produce 5:00 using your first sample.
      In E2 enter =COUNTIF(D2:D35,$F$2)
      In F2 enter the time period you're looking to count.
      So, you're telling Excel to look in the range D2 thru D35 for the value in F2. In the formula the F2 address is locked.
      The cells holding the time should be formatted in the same type. I used Time 1:30PM for my practice sheet. This allowed me to quickly distinguish between AM and PM times.
      I hope this works for you. Let me know if there's something else.

  12. Hi
    I have a excel spreadsheet of members of a club I am running, the spreadsheet contains members details each member has a unique reference number ie, 1243199 etc etc, the members may then have joined more than one group within the club.

    I need to be able to show how many members are members of either 1, 2, 3, 4, 5, 6, or 7 groups.

    Thanks for your help

    1. Simon:
      Without having your data in front of me I would say if you select the cells that contain the data you want to analyze and then click Insert/Table you can format the data into a table that you can quickly and easily sort and filter by member or club or whatever information is in the table.
      If the columns don't already contain headers for each
      piece of information, go ahead and create headers before you create the table. It makes things much easier to work with.
      Depending on the number of members in your club, it might also be easy to create a Pivot Table to analyze this.
      Base your Pivot Table on a table and the changes in the table will be reflected automatically in the Pivot Table.
      There is a good explanation of Pivot Tables here on AbleBits. Just type "Pivot Table" in the search box and you'll see the link to Pivot Table for Beginners article.

  13. HI...
    I was tried the formula for sum product
    Eg.,
    1+2+3+4. this is A Column Values.
    I need the Value of Total in B column.
    May I know the Formula pls

    1. Abi:
      If I understand your question, where the data in Cells A1:A4 are 1,2,3,4 then in Cell B1 the formula is =Sum(A1:A4)

  14. Hi,
    How to count text mentioned as ''yes'' in different cells Example : count of Yes in cell No. A2 +A20+B15+c30+D25
    Please help

    1. Hello,

      Please try the following formula:

      =COUNTIF(A2,"=yes")+COUNTIF(A20,"=yes")+COUNTIF(B15,"=yes")+COUNTIF(C30,"=yes")+COUNTIF(D25,"=yes")

      Hope it will help you.

  15. Hello,

    2 questions:

    1. How to count cells in a row with certain color/s (I use only 2 colors) and return the result in another tab (summary).
    2. How to make sure that additional rows in a sheet are counted? I can't highlight the whole column (as range) as I include notes at the bottom of the working area. At the moment, I specify a range but when my list goes beyond the range, and I forget to update the formula, I miss the additional rows and my count is wrong.

    Appreciate the help. Thank you.

    1. Hello, Sarah,

      1 Here's a short tutorial about counting cells depending on their colours :)
      2 Perhaps, you could extend the range in the formula, but I'm not entirely sure whether you need all the cells from additional range to count or only some of them. And if only some of them, how you decide which ones?

  16. Please i need a formula for a discount of $37.9 given when a purchase below 49lbs to 47lbs and also a discount of $75.8 is given when a purchase below 47lbs to 46lbs is been made. please i need a formula fo this please

    1. Hello.

      Thank you for contacting us.

      Please try the following formula to solve your task:

      =IF(AND(A1 >= 47,A1 <= 49), "$ 37.9", IF(AND(A1 >= 46, A1<47), "$ 75.8", 0))

  17. Could someone please suggest a formula for the following requirement:

    For Example: I have in Column range from B5 to B20 and B5, B6, B7, are with numerical data, and B8, B9 are blank, B10,B11 again with data.

    I want to have a formula which counts all the cells from B5 to the last active column which is B11 and the answer should be 7 for this – which is by counting all the way from B5 to B11. Many thanks.

    1. Dear all,

      I just got the formula which I was looking for, from another site:

      The following formula gives count of all the blank and non-blank, till the last cell with any data:
      =MAX(IFERROR(MATCH("ž", A:A),0),IFERROR(MATCH(1E+100, A:A),0))

      The following formula give the count of all the blank and non-blank cells till the last cell with the figures
      =MATCH(1E+100,A:A)

      Hope this would be helpful to someone with the same requirement as mine. God Bless. Thomas

  18. Hello
    I havew in my column A the fellowing values
    B-COM-TES-001
    V-MAN-ITF-005
    S-COM-TES-020
    V-COM-TES-008

    if I want to count the combine (COM-TES)In all the column
    How can I do

    Merci

    1. I have date in cell like below:
      TC1D 2
      TC1-D 5
      I want to count the date cell for both at a time and sum of the same.
      In that way like my count will come 2 and sum 7.
      Please help.

    2. =COUNTIF(J4:J7,"*com*")
      j4:j7 is data coloumn.

  19. how can I make formulas appear in all cells instead of the values

    1. ctrl+`

  20. Hi Mam
    This list is % or marks
    100
    93
    91.4
    90.8
    90.6
    90
    88.6
    86.4
    85.2
    80.8
    79.2
    78.4
    75.8
    75
    73.6
    73.6
    73.6
    73.6
    73.6
    73.6
    73.6
    73.6
    71.6
    71.6
    71.4
    70
    69.6
    68.6
    66.2
    65.2
    I want result between 91 to 100 how many student
    81 to 90
    71 to 80
    61 to 70
    51 to 60
    41 to 50
    33 to 40
    below 33
    what formula use in it.

    1. =countifs(range of mark,">=71",range of mark,"<=80")

Post a comment



Thanks for your comment! Please note that all comments are pre-moderated, and off-topic ones may be deleted.
For faster help, please keep your question clear and concise. While we can't guarantee a reply to every question, we'll do our best to respond :)