by 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:
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.
- 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.
- 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:
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)
Tips:
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:
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:
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:
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:
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:
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:
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:
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)
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!
113 comments
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!
Extremely Helpful website.
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??
how can I make formulas appear in all cells instead of the values
ctrl+`
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
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
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
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.
=countifs(range of mark,">=71",range of mark,"<=80")
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.
I found the solution, disregard, thank you.
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.
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.
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
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
=COUNTIFS(G2:G11,"****reddy",H2:H11,"delhi")
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
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 ?
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")
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
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.
Hi Rohan,
Please check out the following tutorials:
How to create an Excel drop down list: static, dynamic, from another workbook
How to make a cascading (dependent) drop down list in Excel
Pretty good ma'm, bundle of thanks :)
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","")))
Please reply
CUSTOMER1(CONTAINER1) AVAILABLE
CUSTOMER2(CONTAINER1) AVAILABLE
CUSTOMER1(CONTAINER2) AVAILABLE
CUSTOMER2(CONTAINER2) AVAILABEL
ANSWER SHOULD BE CUSTOMER1 2