{"id":246,"date":"2014-08-03T12:12:16","date_gmt":"2014-08-03T04:12:16","guid":{"rendered":"http:\/\/excelchamp.net\/?p=246"},"modified":"2023-12-03T21:36:57","modified_gmt":"2023-12-03T13:36:57","slug":"multiple-ways-to-count-in-excel","status":"publish","type":"post","link":"https:\/\/www.excelchamp.net\/multiple-ways-to-count-in-excel\/","title":{"rendered":"Multiple Ways to Count in Excel"},"content":{"rendered":"

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.<\/p>\n

Let’s look at the different ways to count in Excel, and their relative benefits.<\/p>\n

1. Count the number of cells with values:<\/strong> 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.<\/p>\n

Count only works with counting of Numeric information. If you try to count names, you will get a ZERO.<\/p>\n

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.<\/p>\n

\"Count<\/a>
Count of Numbers in a Range, & Count of Names in a Range<\/figcaption><\/figure>\n

2. Using a Criteria to Count specific Cells:<\/strong> 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.<\/p>\n

=DCOUNT(Database, Field, Criteria)<\/em><\/p>\n

\"Count<\/a>
Count the cells that meet a specific criteria using Dcount<\/figcaption><\/figure>\n

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.<\/p>\n

The answer for this count is 5. See if you got this correct.<\/p>\n

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.<\/p>\n

3. Using COUNTIF Function to count cell conditionally.<\/strong> This method evaluates the cells against the condition, and if it matches, it is counted.<\/p>\n

=COUNTIF(Cells, criteria)<\/em><\/p>\n

\"Count<\/a>
Count cells that match a condition<\/figcaption><\/figure>\n

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.<\/p>\n\t\t

\n\t\t\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t

Want to Improve Your Excel Skills?<\/strong><\/span><\/p>

Learn the Key Features of Excel Quickly & Easily, by <\/span>Joining the
Online Training<\/strong> on <\/u>Basic \/ Intermediate Excel<\/u>.<\/strong><\/a><\/span><\/p>

To Get Most out of Excel, Learn the\u00a0 Pivot Table techniques in our
Pivot Table Masterclass Training<\/a><\/u><\/b>!<\/strong><\/a><\/span><\/p><\/div>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t<\/div>\n\t\t\n

And if you have multiple criteria to check for, you can use the COUNTIFS which can take multiple criteria arguments.<\/p>\n

\"CountIFS<\/a>
CountIFS to count cells with multiple criteria cells.<\/figcaption><\/figure>\n

The formula in the picture captures the count of cells that are having age of >30years, and Salary less than $3000. Answer is 3.<\/p>\n

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.<\/p>\n

Use these functions to count in multiple ways… Happy counting!<\/p>\n

Cheers
\nVinai Prakash
\n\t\t

\n\t\t\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t
\n\t\t\t

Are you facing any problem in using Excel? Any Question?<\/h3>\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t

You have come to the right place<\/span>. Tell us your needs. We’ll be glad to help you!<\/span><\/strong><\/p>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t

\n\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t\n\t\t\t\n\t\t\t\n\n\t\t\t\n\t\t\t
\n\t\t\t\t\t\t\t\t
\n\t\t\t\t\t\t\t\t\t\t\t\t