XL Counting


XL Counting

Counting number and text values

The following sections summarize the specific tasks for counting number and text values, the best Office Excel feature for each task, and a link to one or more help topics for specific examples and information.

If you want to	See	Comments
Count cells in a column or row by using AutoSum	Count cells that contain numbers ("Count cells that contain numbers in a contiguous row or column" section)	Often, all you need to do is just click AutoSum to get the results you want.
Count cells in a range by using the COUNT function	Count cells that contain numbers ("Count cells that contain numbers that are not in a contiguous row or column" section) COUNT	The COUNT function comes in handy when you want to count values from different ranges or combine literal values with ranges of values.
Count cells in a column by using an outline	Insert subtotals Remove subtotals Calculating subtotals and working with levels in Excel	If your data is in a list and you can logically group it by column values, you can create an outline and then use the Subtotals command on the Data menu to lighten your chores.
Count cells in a column or row by using a PivotTable	About calculations and formulas in PivotTable and PivotChart reports Summary functions available in PivotTable and PivotChart reports Display or hide totals in a PivotTable report	PivotTables have built-in features that make counting values in columns and rows a breeze.
Count cells in a list column by using the SUBTOTAL function	SUBTOTAL	The SUBTOTAL function can control whether or not hidden rows are included in the results and always ignores rows that have been filtered.

Counting with one or more criteria

If you want to	See	Comments
Count cells in a range based on a single criteria by using the COUNTIF function	COUNTIF Count numbers greater than or less than a number	You can easily count cells based on a single criteria with the COUNTIF function.
Count cells in a column based on single or multiple criteria by using the DCOUNT database function	DCOUNT	Use the DCOUNT function when you have a column list and you find it easier to define your criteria in a separate range of cells, rather than use a nested function.
Count cells in a range based on multiple criteria by using the COUNT and IF functions	Count how often a value occurs	There's no one function or feature that can do this task, so your best bet is to nest the COUNT and IF functions.

If you want to	See	Comments
Count nonblank cells in a range by using the COUNTA function	Count nonblank cells COUNTA	When counting cells, sometimes you want to ignore any blank cells because only cells with values are meaningful to you. For example, you want to count all salespeople who made at least one sale in a region.
Count nonblank cells in a list by using the DCOUNTA function	DCOUNTA	Count the nonblank cells in a list column or the entire list.
Count blank cells in a contiguous range by using the COUNTBLANK function	COUNTBLANK	When counting cells, sometimes you want to include blank cells because they are meaningful to you. For example, you want to count all salespeople in a region, whether or not they made a sale.
Count blank cells in a discontiguous range by using the SUM and IF functions	XL: When to Use SUM(IF()) instead of CountBlank()	Agreed, it's not obvious to use the SUM and IF functions instead of COUNTBLANK, but this topic explains how.

Counting unique occurences

If you want to	See	Comments
Count unique values in a range by using a PivotTable	Count how often a value occurs ("Count how often multiple values occur by using a PivotTable report" section)	Use PivotTable report totals and counts and you're on your way.
Count unique values in a range based on a single criteria by using the COUNTIF function	Count how often a value occurs ("Count how often a single value occurs by using a function" section)	You can easily count unique cells with the COUNTIF function.
Count unique values in a range based on multiple criteria by using the SUM and IF functions	Count how often a value occurs ( "Count how often multiple text or number values occur by using functions" section) XL: Using SUM(IF()) As an Array Function Instead of COUNTIF() with AND XL: How to Count the Occurrences of a Number or Text in a Range	There's no one function or feature that can do this task, so your best bet is to nest the SUM and IF functions.
Count the number of unique values in a list column by using Advanced Filter	Count unique values among duplicates ("Count the number of unique values by using a filter" section)	The simplest way to do this is by using the Advanced Filter subcommand from the Filter submenu on the Data menu.
Count the number of unique values in a range with one or more criteria by using a mega-formula	Count unique values among duplicates ("Count the number of unique values by using functions" section) How to determine the number of unique items in a list	Take a deep breath, crack your knuckles, and put your thinking cap on for this mega-formula.

Special cases (running count, count all cells, count words)

If you want to	See	Comments
Do a running count by using a formula	Calculate a running total	Watch your totals tally right before your very eyes.
Count the total number of cells in a range by using the ROWS and COLUMNS functions	Count all of the cells in a range	Impress you colleagues by always knowing exactly how many cells that monthly sales spreadsheet is using.
Count words in a range by using a mega-formula	Count the number of words in a cell or range	Do a word count in Office Excel as easily as Microsoft Office Word.

See Also

	XL: Some Worksheet Functions Do Not Allow Array Constants

	XL2000: COUNTIF Returns Incorrect Value When It Refers to Blank Cells

	XL2000: SUMIF and COUNTIF Functions Return Incorrect Results When Used with Strings Longer Than 255 Characters

	XL2000: SUMIF, COUNTIF, COUNTBLANK Return #VALUE! Error

	XL2002: AutoSum Button Unexpectedly Returns Values from Hidden Cells in List with AutoFilter

Order a book or two today - you can download an eBook today for only $1.99!

Just click on a book to learn more!