Counting the number of cells containing values, counting number of cells meeting a certain criteria, counting names, counting cells in a range…. you name it, there is a need to count in Excel. And there are multiple ways to count in Excel. Some techniques are more helpful than others, and some can provide unique insights which others can’t produce.
Let’s look at the different ways to count in Excel, and their relative benefits.
1. Count the number of cells with values: The simplest way is to use the inbuilt function Count. Select the cell range, and out comes the cou nt of cells in the range.
Count only works with counting of Numeric information. If you try to count names, you will get a ZERO.
To count alphanumeric values – values with names, categories, text, serial numbers etc., then you must use the variation of Count – called COUNTA. This function is to count Alphanumeric data.
2. Using a Criteria to Count specific Cells: There are several ways to count the cells that meet a certain criteria. The first method treats the data as a database, and uses the Database Functions within Excel.
=DCOUNT(Database, Field, Criteria)
Here A4:C12 is the entire data, B4 is the cell column that we want to count, and the criteria is Age should be greater than 30 years (B1:B2) cells.
The answer for this count is 5. See if you got this correct.
The database functions have been existing in Excel since 1995. They treated Excel data as a database like Oracle, Dbase etc. These database functions DSUM, DCOUNT, DAVERAGE etc. are quite useful, and are available for backward compatibility, although the same work can be accomplished by other functions equally well too.
3. Using COUNTIF Function to count cell conditionally. This method evaluates the cells against the condition, and if it matches, it is counted.
=COUNTIF(Cells, criteria)
Here, the cells that contain the data is provided first, and the second argument is the criteria. The headings are not really needed. Each cell in the first range is checked against the criteria, and if the criteria matches, the record is counted, otherwise it is ignored.
Want to Improve Your Excel Skills?
Learn the Key Features of Excel Quickly & Easily, by Joining the
Online Training on Basic / Intermediate Excel.
To Get Most out of Excel, Learn the Pivot Table techniques in our
Pivot Table Masterclass Training!
And if you have multiple criteria to check for, you can use the COUNTIFS which can take multiple criteria arguments.
The formula in the picture captures the count of cells that are having age of >30years, and Salary less than $3000. Answer is 3.
So as you can see, there are multiple functions, and ways to count cells in Excel. It depends on whether you want to count numbers or textual data. It also depends if you have conditions or not.
Use these functions to count in multiple ways… Happy counting!
Cheers
Vinai Prakash
Are you facing any problem in using Excel? Any Question?
You have come to the right place. Tell us your needs. We’ll be glad to help you!
