Mar
30

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 the modern versions of Excel 2016, Excel 2013, Excel 2010, and Excel 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 cells with values (non-blank cells)

The COUNTA function in Excel counts the number of cells in a range 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

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

Excel COUNTIF function - count cells that meet one condition

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 about using the COUNTIF function in Excel, check out the following tutorial:
COUNTIF in Excel - count if not blank, greater than, duplicate or unique

Excel COUNTIFS function - count cells that match several criteria

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, 2013, and 2016.

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

And again, 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: How to use Excel COUNTIFS function with multiple criteria.

Count the number of cells in a range (ROWS and COLUMNS functions)

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!

You may also be interested in:

47 Responses to "Excel COUNT and COUNTA functions to count cells"

  1. rinju says:

    how can we use "if" function when there is either text or number in cell..

    if the cell contain text then answer should be "Yes" or else the cell contain number then answer shold come "No"

    • Hello Rinju,

      You can use a formula similar to this:

      =IF(ISTEXT(A1), "Yes", IF(ISNUMBER(A1), "No", ""))

      Additionally, you can check for blank cells:

      =IF(ISTEXT(A1),"yes", IF(ISNUMBER(A1),"no", IF(ISBLANK(A1),"blank","")))

  2. Slamat Ali says:

    Pretty good ma'm, bundle of thanks :)

  3. Rohan Walimbe says:

    can u send me a drop down list examples in excel where there is huge data for e.g

    Divison Zone Region Area HQ
    Divan West Mumbai Mumbai Mumbai
    Divan West Mumbai Mumbai Goa
    Divan West Pune Pune Pune
    Divan West Pune Pune Pimpri
    Divan West Ahmdabd Rajkot Rajkot
    Siman North Delhi dELHI Delhi
    Siman North Delhi jAIPUR Kota
    Siman North Delhi Jaipur Jodhpur

    This list is huge. Please help how to prepare drop down list

    if we click on Division the Divison will show then after clicking on Zone the zone and clicking on the specific zone the zones area will reflect with HQ automatically.

    Please help.

  4. mike says:

    I have three worksheets two with the years dates in B3:NB3. the a1 column has text in it. I am trying to calculate how many time in a rolling 90 days the product in a1 sell mp I'm using this
    =COUNTIFS(A1,"<=today()-90",'2016'!B4:NB4,"MP")

    It count the MP but
    ie if today is 5/24/16 it should not count any mp before 2/27/16

    can you tell me what I'm missing. I'm getting "Value" in the output field

    thanks

  5. Khurrum Zeeshan says:

    I was Searching this topic since 3 weeks Now i Found in this articular Thanks a lot. its working in Drop Down list active Words.
    =COUNTIF(A2:A15, "apples")

  6. KJ says:

    Hello

    i want to count Yes and No in the rows

    That was no issue .
    But i want to stop the you after the first the NO

    has sombody a idea how to handle it ?

  7. Riaan says:

    I have want to count a range of cells. The =COUNTA('Employee Edu-Skills History'!A31:A55,"*")count the formulas. I only want to count the text in the cells

  8. DEBIPRASAD says:

    HOW TO COUNT, I HAVE DATA OF STUDENTS FROM VARIOUS STATES, I WANT TO COUNT HOW MANY STUDENTS ARE FROM DELHI AND THERE SURNAME IS "REDDY" ? PLZ HELP

  9. Navin says:

    Write a Formula to count the number of Active Employees in Each City

    Employees Location Status
    Employee1 Manila Active
    Employee2 Manila Active
    Employee3 Mumbai Active
    Employee4 Surrey Active
    Employee5 Mumbai Inactive
    Employee6 Mumbai Active
    Employee7 Mumbai Active
    Employee8 Manila Inactive
    Employee9 Manila Active
    Employee10 Surrey Inactive
    Employee11 Surrey Active
    Employee12 Surrey Active
    Employee13 Manila Active
    Employee14 Manila Active
    Employee15 Manila Active
    Employee16 Manila Active
    Employee17 Manila Inactive
    Employee18 Surrey Active
    Employee19 Surrey Active
    Employee20 Mumbai Active

  10. Hema says:

    I want to count enteries in a column and then multiple the number of entries by a set number. In other words, I have a spreadsheet, where different columns have different values. IF an entry is made in column A, it should be counted as 1 entry. But the value of second column is double and that of third is x 5.

    So I want to be able to count the number of entries in a particular column and then multiply it by 2/5/10. Whats the best formula for this?

    Thanks for your help.

  11. Asif says:

    I am using this formula =IF(MAX($A$4:A263)=SUMPRODUCT(1/COUNTIF($B$5:B264,$B$5:B264)),"",(SUMPRODUCT(1/COUNTIF($B$5:B264,$B$5:B264)))) For Serial Number but it shows the sr no after missed some columns. whereas the sr. number column should be nill until the next particular not comes.

  12. LouieUSA says:

    Hello I am trying to find a formula which allows me to add 2 different words in 2 different columns with drop down menus in each. Here are an example of the words:

    "A" for Column E

    For Column G
    ACV, AMV, FIRE, PER INJ, OTHER, PMV2, PMV4, PMVO, PRO DMG, SUPLMT, WATER, WPN/ND, AV GND

    A certain combination will only work for a specific column. Example:
    Accident Class:
    A
    B
    C
    D
    E
    F
    OHR
    NR
    Each of these only work with one word at a time, but all of the combinations for "A" need to be tracked in a column (A3) for example.
    "B" would tracked in A4 and "C" would be tracked in A5 and so on. If B and ACV were in their respective columns (Say E5 and G5)on one sheet, then those 2 numbers would be tracked on another sheet on A3 as a number.

    I hope this explains it well enough, thank you.

  13. Yash says:

    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.

  14. Roni says:

    Hi,
    I am trying to use countifs to count no of cell which meet multiple criteria where each criteria has got different no fo rows and column compare to other.
    for example:
    i want to count no of cell from column D,E and F if value in column A is equal to X.
    Thanks in advance

  15. Andy says:

    Ok guys/girls i'm pulling my hair out with this formula, i'm fairly confident the problem is in the two cells with times in. cells h50:h5000 have two times combined in a cell with - as a division, all i need is the second part but using 'right' function doesn't appear to work as part of a larger countifs. these cells are imported and can be 1000's of rows long so creating another column to separate is awkward.

    f50:f5000=13:59 h50:h5000=12:33-17:00

    =COUNTIFS(C$50:C$5000,A6, X$50:X$5000, ">0", Y$50:Y$5000, ">0",F$50:F$5000, ">" & RIGHT(H$50:H$5000,5))

    The formula by itself (ctrl, shift, enter) appears to work fine but I require the other criteria.

    =COUNTIF(F$50:F$5000, ">" & RIGHT(H$50:H$5000,5))

    Any help would be greatly appreciated.

    Andy

  16. Monali wagh says:

    I have query divide this no as an following way in different column if 4,50,00, 000 given then we will get
    2,00,00,000
    1,00,00,000
    1,00,00,000
    50,00,000

  17. sililo says:

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

  18. zaidan says:

    Hi, i have a certain name (wilium) in column A and against this name i have 2 option, 1 Done and 2 Inprogress. i want that i count done again wilium and count inprogress against wilium separately. which formula will work for it??

  19. Ashfaq Ahmed Khan says:

    Extremely Helpful website.

  20. Domes says:

    Hi,

    I would love to have some help on this:

    I have B5:B66 range and I want count ONLY if a certain value occurs 3 times or more (values 8,0

    Example: 8599962555
    I need to count: 999 = 1
    and: 555 = 1
    Then : 1+1 = 2

    How can I do this???
    thanks in advance!

  21. SUDHEER K B says:

    I have data with family includes Self, spouse, children, parents. i want separate count of how many single child family and how may 2 children family are there in data.

    kindly help

  22. sydney says:

    Hi there,

    I want to count the number of blank cells and non-blank cells in column B,
    if column A is X and column C is X, Y and Z.

    Thank you

  23. Robert says:

    Hello,

    Do you also have a tutorial on COUNT and COUNTA in Google Sheets?

    Yours,

    Robert

  24. me says:

    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

  25. Lorna Fong says:

    Svetlana - thank you for your blog. Your help on an array three years ago helped me greatly on my project. I have a new COUNTIF question. I have a file with 9895 rows of mailing information. The zip codes are either 5-digits or zip+4. I'm trying to count the number of zip codes in a few categories: 1) Number of 5-digit zip codes (I wrote that formula fine); 2) Number of zip codes that BEGIN with THREE specific numbers and DO NOT include any of the 5-digit zip codes I already counted. I can't seem to write a formula or array that gets me to the correct answer. I don't want to count any zip codes that INCLUDE the three specific numbers if they are NOT the first three numbers. I've used LEFT for a few formulas, but now I can't seem to eliminate those zip codes where the specific string is INSIDE the zip code, not the first three characters in the cell. Should the column of zip codes be a number or text? Here's my array attempt at counting: =SUM((LEN(LABELS!$B$2:$B$10000)-LEN(SUBSTITUTE(LABELS!$B$2:$B$9896,"958","")))/LEN("958"))-G2-G3-G4-G7-G8-G9-G10-G11-G12 that I thought searches for zip codes that begin with "958" and subtracts the 5-digit searches I already completed, but it's returning a count with the "958" included in the zip. Any thoughts?

  26. madu says:

    I want to count values those are in following ranges
    0 -19 ,20-34, 35-39,40-49,50-64,65-74,>75

  27. Thomas M. Jayabhai says:

    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.

    • Thomas M. Jayabhai says:

      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

  28. Anna Johnson says:

    Can someone help me?

    I have a column that has different cities in each cell some have just one city and some have Multiple destinations: city1, city2, city3. These cities overlap. Is there a function I can use to count all of "city 1"? I have tried =COUNTIF(F4:F29,"City 1 name") but it does not count the ones with multiple cities.

    Thanks,

  29. najam says:

    many number count

  30. dhor deng says:

    hello !
    I need more excel work in today, vlookup and count

    thanks in advance

  31. ishmail dumbuya says:

    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

    • Mary Trifuntova (Ablebits.com Team) says:

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

      Feel free to contact us again if you need any further assistance.

  32. ishmail dumbuya says:

    Thank you so much for your reply, really appreciate it. I tried the formula but it doesnt give me what am looking for.
    This is the summary of the formula i need.
    discount of $37.9/MT for every pound below 49lbs to 47lbs. And discount of $75.8/MT below 47lbs to 46lbs and rejection for outturn below 46lbs.
    Discount of $5/MT for every nut above 210 to 215, and above 215 to be rejected.
    Discount of $20/MT for every 1% above 10% to 12% above 12% to be rejected. please i need this formula assistance. Thank you.

  33. Ashok says:

    Can someone help me?

    I need to Formal for Countif Formal
    Ex : Our Employees Duty Roster so Shift Based Job thats reason i Count Different Cell Select then Count How many Employees there Time was There LIke Please Help me

  34. Jabrony says:

    Hi, I'm looking for a formula where it only COUNTS a certain criteria in Sheet 2 if it matches my formula in Sheet 1 B2 which is: =COUNTIF(Sheet 2!$A$1:$A$120,"Dog")

    So imagine Sheet 1 B2 having the results already, I now wanted to count either A, B, or C in Sheet 2 if it EXACTLY matches the name I'm looking for in Sheet 1 A1.

    e.g: Sheet 1 A1=Dog B1=(count of Dog in Sheet 2) C1=A D1=B E1=C
    Sheet 2 A1,A4,A7,A10,A11=Dog
    Sheet 2 A2,A5,A8,A12=Cat
    Sheet 2 A3,A6,A9,A13=Sheep

    Sheet 2 B1=A C1=A D1=A
    Sheet 2 B4=B C4=A D4=A
    Sheet 2 B7=B C4=B D4=A
    Sheet 2 B10=A C10=A D10=A
    Sheet 2 B11=A C11=B D11=A

    So with this sample given, Sheet 1 B1 counted all "Dog" in Sheet 2. Sheet 1 B1,C1, and D1 is looking for A, B, and C, if all rows in in Sheet 2 says:
    [Dog][A][A][A]
    [Dog][B][A][A]
    [Dog][B][B][A]
    [Dog][A][A][A]
    [Dog][A][B][A]

    Thank you!!!

  35. Sarah Jane Santillan says:

    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.

  36. Man in Problem says:

    Dear,

    How to use countifs function in I have sevaral answers in one column that will be counted?

    More precisely, if I want to count only "Yes" answers from one column and "Yes, always" and "Yes, sometimes" answers from another.

    Thank you in advance...

  37. Felicia says:

    Im trying to create a formula that counts yes and no without counting them more than once. The yes can apply to the same person but only need to count it once.

  38. shamraiz says:

    Hello Hi
    Please sent my formoula
    for exmplale
    500-2=498 after one entry amount 498 next new figer enter in cell for
    exmpale 3 = 498-3= 495 amount
    plese sent me simple formoula

  39. Raja says:

    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

  40. Anup says:

    how to keep attendance daily wise even multiple visit count 1 per day in excel please help me...

    Name Date Visit
    Ram 2017/12/1 1
    Shyam 2017/12/1 1
    Hari 2017/12/1 1
    Ram 2017/12/1 1
    Ram 2017/12/2 1
    shyam 2017/12/2 1
    Ram 2017/12/31 1
    shyam 2017/12/31 1

    answer should be

    name month attendance
    ram December 3
    Shyam December 3
    Hari December 1

    how to do????

Post a comment



Unfortunately, due to the volume of comments received we cannot guarantee that we will be able to give you a timely response. When posting a question, please be very clear and concise. We thank you for understanding!
Excel add-ins and Outlook tools - Ablebits.com
Ultimate Suite for Excel Professionals
 
 
60+ professional tools for Excel 2016-2007 to do your daily work
Incredible product, even better tech support…AbleBits totally delivers!
Deborah Bryant
Anyone who works with Excel is sure to find their work made easier
Jackie Lee
The best spent money on software I've ever spent!
Patrick Raugh
Ablebits is a fantastic product - easy to use and so efficient.
Debra Celmer
Excel is at its best now
Annie C.
I don't know how to thank you enough for your Excel add-ins
Jennifer Morningstar
Anybody who experiences it, is bound to love it!
Kumar Nepa
AbleBits suite has really helped me when I was in a crunch!
Nelda Fink
I have enjoyed every bit of it and time am using it
Christian Onyekachi Nwosu
It's the best $100 we've ever spent!
Mike Cavanagh
I love the program, and I can't imagine using Excel without it!
Robert Madsen
One word… WOW!
Dave Brown
Love the products!
David Johnston
It is like having an expert at my shoulder helping me…
Linda Shakespeare
Your software really helps make my job easier
Jeannie C.
Thanks for a terrific product that is worth every single cent!
Dianne Young
I love your product
Brad Gibson
Awesome!!!
Sheila Blanchard