Microsoft Office Online
Sign in to My Office Online (What's this?) | Sign in

 
 
Microsoft Office Excel
Search
Search
 
Check for updates: (c) Microsoft
Office downloads
 
 
 
Product Information
Help and How-to
Choose your version
Training
Templates
Related Products and Technologies
Support and Feedback
Technical Resources
Additional Resources
Warning: You are viewing this page with an unsupported Web browser. This Web site works best with Microsoft Internet Explorer 6.0 or later, Firefox 1.5, or Netscape Navigator 8.0 or later. Learn more about supported browsers.
Email this linkEmail this link Printer-Friendly VersionPrinter-Friendly Version Bookmark and ShareShare
Summing up ways to add and count Excel data
 
Applies to
Microsoft Office Excel 2003

Adding and countingAdding and counting data is the bedrock of data analysis, whether you are tallying an exact head count of an organization, subtotaling sales in the Northwest region, or doing a running total of weekly receipts. Microsoft Excel documents more ways to add and count than you can shake a stick at. That's great, because it means there's likely a help topic that explains how to do what you want. But, it isn't always obvious which help topic to use.

To help you make the right choice, here's a comprehensive summary of links to each adding and counting help topic. Each section has a decision table to help you quickly find the information you're looking for.

 Tip   Don't forget to add this article to your favorites list, so you can always count on it to help you find the topic you're looking for.

Adding numbers

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

ShowSimple addition

If you want to See Comments
Add values in a cell by using a formula Add numbers
("Add numbers in a cell" section)

Let Excel be your calculator

 Enter a formula into a cell and use it as a mini-calculator.
Add values in a column or row by using AutoSum Add numbers
("Add all contiguous numbers in a row or column" section)

Let Excel be your calculator

 Often, all you need to do is just click AutoSum AutoSum button to get the results you want.
Add values in a range by using the SUM function Add numbers
("Add noncontiguous numbers" section)

SUM

 The SUM function comes in handy when you want to add values from different ranges or combine number values with ranges of numbers.
Add values 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 you chores.
Add values 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.
Add values 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 adding columns and rows a breeze.

ShowAddition with one or more criteria

If you want to See Comments
Add values in a range based on a single criteria by using the SUMIF, or SUM and IF functions SUMIF

IF
(Contains an example for deciding whether or not to add values)

Add numbers
("Add numbers based on one condition" section)

XL: When to Use SUM(IF()) instead of CountBlank()

 You can add numbers based on a single criteria with the SUMIF function, but sometimes it's better to nest the SUM and IF functions if you need more flexibility.
Add values in a column based on single or multiple criteria by using the DSUM database function Add numbers
("Add numbers based on criteria stored in a separate range" section)

DSUM

 Use the DSUM 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.
Add values in a range based on multiple criteria by using the Conditional Sum Wizard Add numbers
("Add numbers based on multiple conditions with the Conditional Sum Wizard" section) 		Feeling lazy? Let the Conditional Sum Wizard (available from the Conditional Sum command on the Tools menu) add values based on multiple criteria. If this command is not available, install and load the Analysis ToolPak add-in.

ShowHow?

  1. On the Tools menu, click Add-Ins.
  2. In the Add-Ins available list, select the Analysis ToolPak box, and then click OK.
  3. If necessary, follow the instructions in the setup program.
Add values in a range based on multiple criteria by using the SUM and IF functions Add numbers
("Add numbers based on multiple conditions" section)

XL: How to Use a Logical AND or OR in a SUM+IF Statement in Excel

XL: When to Use SUM(IF()) instead of CountBlank()

 There's no one function or feature that can do this task, so your best bet is to nest the SUM and IF functions.

ShowAddition of squared and array values

If you want to See Comments
Add the product of corresponding values in multiple arrays SUMPRODUCT The SUMPRODUCT function is great for values in arrays with the same dimensions, but you can also use the SUM function with a formula array if you need more flexibility.
Add the square of each value in a range SUMSQ These calculations are often used with statistics and complex math.
Add the sum of the square of corresponding values in two arrays SUMX2PY2
Add the difference of the square of corresponding values in two arrays SUMX2MY2
Add the square of the difference of corresponding values in two arrays SUMXMY2

ShowSpecial cases (running total, unique values)

If you want to See Comments
Do a running total by using the SUM function Calculate a running balance Watch your numbers add up right before your very eyes.
Add unique values in a range by using a mega-formula Add numbers
("Add unique values" section)		 Feeling creative? Here's a clever use of the FREQUENCY function that will do the trick for you.

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.

ShowSimple counting

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 AutoSum button 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.

ShowCounting 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.

ShowCounting with blank values

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.

ShowCounting 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.

ShowSpecial 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.

Related Office Online discussions

Read related questions and answers from other Microsoft Office customers.
Printer-Friendly VersionPrinter-Friendly Version