## Multiple Ways to Count in Excel

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

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

## Simple Functions in Excel – Video

Most beginners to Microsoft Excel are not aware how easy it is to use some simple functions.

I have recorded a detailed, step by step video, which shows how to use the Sum, Count, Average, Minimum and Maximum functions in Excel, to add, count numbers.

Want to Improve Your Excel Skills?

Learn the Key Features of Excel Quickly & Easily, by

To Get Most out of Excel, Learn the  Pivot Table techniques in our
Pivot Table Masterclass Training
!

Do let me know if this helps you in using Microsoft Excel. The techniques applied here are same for Excel 2003, Excel 2007, Excel 2010 or Excel 2013, Excel 2016 & Office 365, so it does not matter which version you are using.

And check out my other Microsoft Excel Tips & Tricks videos on YouTube.

Cheers,
Vinai Prakash