Change formula recalculation, iteration, or precision

To use formulas (formula: A sequence of values, cell references, names, functions, or operators in a cell that together produce a new value. A formula always begins with an equal sign (=).) efficiently, there are three important considerations that you need to understand:

Calculation   is the process of computing formulas and then displaying the results as values in the cells that contain the formulas. To avoid unnecessary calculations, Microsoft Office Excel automatically recalculates formulas only when the cells that the formula depends on have changed. This is the default behavior when you first open a workbook and when you are editing a workbook. However, you can control when and how Excel recalculates formulas.

Iteration   is the repeated recalculation of a worksheet until a specific numeric condition is met. Excel cannot automatically calculate a formula that refers to the cell — either directly or indirectly — that contains the formula. This is called a circular reference. If a formula refers back to one of its own cells, you must determine how many times the formula should recalculate. Circular references can iterate indefinitely. However, you can control the the maximum number of iterations and the amount of acceptable change.

Precision   is a measure of the degree of accuracy for a calculation. Excel stores and calculates with 15 significant digits of precision. However, you can change the precision of calculations so that Excel uses the displayed value instead of the stored value when it recalculates formulas.

What do you want to do?

Change when a worksheet or workbook recalculates

Recalculate a worksheet or workbook manually by using keyboard shortcuts

Change the number of times Excel iterates a formula

Change the precision of calculations in a workbook

Change the number of processors used to calculate formulas

Learn about calculating workbooks that were created in an earlier version of Excel

Change when a worksheet or workbook recalculates

As calculation proceeds, you can choose commands or perform actions such as entering numbers or formulas. Excel temporarily interrupts calculation to carry out the other commands or actions and then resumes calculation. The calculation process may take more time if the workbook contains a large number of formulas, or if the worksheets contain data tables (data table: A range of cells that shows the results of substituting different values in one or more formulas. There are two types of data tables: one-input tables and two-input tables.) or functions that automatically recalculate every time the workbook is recalculated. Also, the calculation process may take more time if the worksheets contain links to other worksheets or workbooks. You can control when calculation occurs by changing the calculation process to manual calculation.

1. Click the Microsoft Office Button , click Excel Options, and then click the Formulas category.
2. Do one of the following:
   • To recalculate all dependent formulas every time you make a change to a value, formula, or name, in the Calculation options section, under Workbook Calculation, click Automatic. This is the default calculation setting.

Tip  Alternatively, on the Formulas tab, in the Calculation group, click Calculation Options, and then click Automatic.

• To recalculate all dependent formulas—except data tables—every time you make a change to a value, formula, or name, in the Calculation options section, under Workbook Calculation, click Automatic except for data tables.

Tip  Alternatively, on the Formulas tab, in the Calculation group, click Calculation Options, and then click Automatic Except for Data Tables.

• To turn off automatic recalculation and recalculate open workbooks only when you explicitly do so (by clicking Calculate Now under Calculation Options in the Calculation group on the Formulas tab), in the Calculation options section, under Workbook Calculation, click Manual.

 Note    When you click Manual, Excel automatically selects the Recalculate workbook before saving check box. If saving a workbook takes a long time, clearing Recalculate workbook before saving may improve the save time.

Tip  Alternatively, on the Formulas tab, in the Calculation group, click Calculation Options, and then click Manual.

• To manually recalculate all open worksheets, including data tables, and update all open chart sheets, on the Formulas tab, in the Calculation group, click the Calculate Now button.
• To manually recalculate the active worksheet and any charts and chart sheets linked to this worksheet, on the Formulas tab, in the Calculation group, click the Calculate Sheet button.

Changing any of the options affects all open workbooks.

 Note    If a worksheet contains a formula that is linked to a worksheet that has not been recalculated and you update that link, Excel displays a message stating that the source worksheet is not completely recalculated. To update the link with the current value stored on the source worksheet, even though the value might not be correct, click OK. To cancel updating the link and use the previous value obtained from the source worksheet, click Cancel.

 Top of Page

Recalculate a worksheet or workbook manually by using keyboard shortcuts

 Top of Page

Change the number of times Excel iterates a formula

1. Click the Microsoft Office Button , click Excel Options, and then click the Formulas category.
2. In the Calculation options section, select the Enable iterative calculation check box.
3. To set the maximum number of times Microsoft Excel will recalculate, type the number of iterations in the Maximum Iterations box. The higher the number of iterations, the more time Excel will need to recalculate a worksheet.
4. To set the maximum amount of change you will accept between recalculation results, type the amount in the Maximum Change box. The smaller the number, the more accurate the result and the more time Excel needs to recalculate a worksheet.

 Note    Solver and Goal Seek are part of a suite of commands sometimes called what-if analysis (what-if analysis: A process of changing the values in cells to see how those changes affect the outcome of formulas on the worksheet. For example, varying the interest rate that is used in an amortization table to determine the amount of the payments.) tools. Both commands use iteration in a controlled way to obtain desired results. You can use Solver when you need to find the optimum value for a particular cell by adjusting the values of several cells or when you want to apply specific limitations to one or more of the values in the calculation. You can use Goal Seek when you know the desired result of a single formula but not the input value the formula needs to determine the result.

 Top of Page

Change the precision of calculations in a workbook

Before you change the precision of calculations, keep in mind the following important points:

By default, Excel calculates stored, not displayed, values   The displayed and printed value depends on how you choose to format and display the stored value. For example, a cell that displays a date as "6/22/2008" also contains a serial number that is the stored value for the date in the cell. You can change the display of the date to another format (for example, to "22-Jun-2008"), but changing the display of a value on a worksheet does not change the stored value.

Use caution when changing the precision of calculations   When a formula performs calculations, Excel usually uses the values stored in cells referenced by the formula. For example, if two cells each contain the value 10.005 and the cells are formatted to display values in currency format, the value $10.01 is displayed in each cell. If you add the two cells together, the result is $20.01 because Excel adds the stored values 10.005 and 10.005, not the displayed values.

When you change the precision of the calculations in a workbook by using the displayed (formatted) values, Excel permanently changes stored values in cells from full precision (15 digits) to whatever format, including decimal places, is displayed. If you later choose to calculate with full precision, the original underlying values cannot be restored.

1. Click the Microsoft Office Button , click Excel Options, and then click the Advanced category.
2. In the When calculating this workbook section, select the workbook you want, and then select the Set precision as displayed check box.

 Top of Page

Change the number of processors used to calculate formulas

A computer can have more than one processor (it contains multiple physical processors) or can be hyperthreaded (it contains multiple logical processors). On these computers, you can improve or control the time it takes to recalculate workbooks that contain many formulas by setting the number of processors to use for recalculation. In many cases, portions of a recalculation workload can be performed simultaneously. Splitting this workload across multiple processors can reduce the overall time it takes complete the recalculation.

1. Click the Microsoft Office Button , click Excel Options, and then click the Advanced category.
2. To enable or disable the use of multiple processors during calculation, in the Formulas section, select or clear the Enable multi-threaded calculation check box.

 Note    This check box is enabled by default and all processors are used during calculation. The number of processors on your computer is automatically detected and displayed next to the Use all processors on this computer option.

3. Optionally, if you select Enable multi-threaded calculation, you can control the number of processors to use on your computer. For example, you might want to limit the number of processors used during recalculation if you have other programs running on your computer that require dedicated processing time.

How to control the number of processors

• Under Number of calculation threads, click Manual. Enter the number of processes to use.

 Top of Page

Learn about calculating workbooks that were created in an earlier version of Excel

To ensure that older workbooks are calculated correctly, Excel behaves differently when you first open an old workbook than when you open a workbook created in the current version.

When you open a workbook created in the current version, Excel recalculates only the formulas that depend on cells that have changed.

When you use open a workbook that was created in a earlier version of Excel, all of the formulas in the workbook — those that depend on cells that have changed and those that do not — are recalculated. This ensures that the workbook is fully optimized for the current Excel version.

Because complete recalculation can take longer than partial recalculation, opening a workbook that was not previously saved in the current Excel version can take longer than usual. Once you save the workbook in the current version, it will open faster.

 Top of Page

See Also

• Overview of formulas
• Set rounding precision

 

 

Excel > Formula and name basics

Change the date system, format, or two-digit year interpretation

 

Dates are often a critical part of data analysis. You often ask questions such as: when was a product purchased, how long will a task in a project take, or what is the average revenue for a fiscal quarter? Entering dates correctly is essential to ensuring accurate results. But formatting dates so that they are easy to understand is equally important to ensuring correct interpretation of those results.

Important  Because the rules that govern the way that any calculation program interprets dates are complex, you should be as specific as possible about dates whenever you enter them. This will produce the highest level of accuracy in your date calculations.

What do you want to do?

Learn about date calculations and formats

Learn about the two date systems

Change the way two-digit years are interpreted

Change the default date format to display four-digit years

Change the date system

Issue: I'm having problems with dates between workbooks that use different date systems

Learn about date calculations and formats

Microsoft Office Excel stores dates as sequential numbers that are called serial values. For example, in Microsoft Office Excel for Windows, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,448 days after January 1, 1900. Office Excel stores times as decimal fractions because time is considered a portion of a day. The decimal number is a value ranging from 0 (zero) to 0.99999999, representing the times from 0:00:00 (12:00:00 A.M.) to 23:59:59 (11:59:59 P.M.).

Because dates and times are values, they can be added, subtracted, and included in other calculations. You can view a date as a serial value and a time as a decimal fraction by changing the format of the cell that contains the date or time to General format.

For more information, see the following help topics:

• For examples of formulas that use dates in calculations, see the Date and Time section in Examples of commonly used formulas.
• For more information about entering dates, see Enter data manually in worksheet cells.
• For more information about formatting dates, see Display numbers as dates or times.

 Top of Page

Learn about the two date systems

Both Microsoft Excel for the Macintosh and Excel for Windows support the 1900 and 1904 date systems. The default date system for Microsoft Office Excel for Windows is 1900; and the default date system for Microsoft Office Excel for the Macintosh is 1904.

Originally, Excel for Windows was based on the 1900 date system, because it enabled better compatibility with other spreadsheet programs that were designed to run under MS-DOS and Microsoft Windows, and therefore it became the default date system. Originally, Microsoft Excel for the Macintosh was based on the 1904 date system, because it enabled better compatibility with early Macintosh computers that did not support dates before January 2, 1904, and therefore it became the default date system.

The following table shows the first date and the last date for each date system and the serial value associated with each date.

Because the two date systems use different starting days, the same date is represented by different serial values in each date system. For example, July 5, 2007 can have two different serial values, depending on the date system that is used.

The difference between the two date systems is 1,462 days; that is, the serial value of a date in the 1900 date system is always 1,462 days greater than the serial value of the same date in the 1904 date system. Conversely, the serial value of a date in the 1904 date system is always 1,462 days less than the serial value of the same date in the 1900 date system. 1,462 days is equal to four years and one day (which includes one leap day).

 Top of Page

Change the way two-digit years are interpreted

Important  To ensure that year values are interpreted as you intended, type year values as four digits (for example, 2001, not 01). By entering four-digit years, Excel won't interpret the century for you.

If you enter a date with a two-digit year in a text formatted cell or as a text argument in a function, such as =YEAR("1/1/31"), Excel interprets the year as follows:

• 00 through 29   is interpreted as the years 2000 through 2029. For example, if you type the date 5/28/19, Excel assumes the date is May 28, 2019.
• 30 through 99   is interpreted as the years 1930 through 1999. For example, if you type the date 5/28/98, Excel assumes the date is May 28, 1998.

In Microsoft Windows, you can change the way two-digit years are interpreted for all Microsoft Windows programs that you have installed.

1. Click the Start button, and then click Control Panel.
2. Do one of the following:
   • In Windows Vista, click Clock, Language, and Region.
   • In Windows XP, click Date, Time, Language, and Regional Options.
3. Click Regional and Language Options.
4. Do one of the following:
   • In Windows Vista, in the Regional and Language Options dialog box, click the Formats tab.
   • In Windows XP, in the Regional and Language Options dialog box, click the Regional Options tab.
5. Do one of the following:
   • In Windows Vista, click Customize this format.
   • In Windows XP, click Customize.
6. Click the Date tab.
7. In the When a two-digit year is entered, interpret it as a year between box, change the upper limit for the century.

As you change the upper-limit year, the lower-limit year automatically changes.

 Top of Page

Change the default date format to display four-digit years

By default, as you enter dates in a workbook, the dates are formatted to display two-digit years. When you change the default date format to a different format by using this procedure, the display of dates that were previously entered in your workbook will change to the new format as long as the dates haven't been formatted by using the Format Cells dialog box (On the Home tab, in the Number group, click the Dialog Box Launcher).

1. Click the Start button, and then click Control Panel.
2. Do one of the following:
   • In Windows Vista, click Clock, Language, and Region.
   • In Windows XP, click Date, Time, Language, and Regional Options.
3. Click Regional and Language Options.
4. Do one of the following:
   • In Windows Vista, in the Regional and Language Options dialog box, click the Formats tab.
   • In Windows XP, in the Regional and Language Options dialog box, click the Regional Options tab.
5. Do one of the following:
   • In Windows Vista, click Customize this format.
   • In Windows XP, click Customize.
6. Click the Date tab.
7. In the Short date format list, click a format that uses four digits for the year ("yyyy").

 Top of Page

Change the date system

The date system changes automatically when you open a document from another platform. For example, if you are working in Excel for Windows and you open a document that was created in Excel for the Macintosh, the 1904 date system check box is selected automatically.

You can change the date system by doing the following:

1. Click the Microsoft Office Button , click Excel Options, and then click the Advanced category.
2. Under the When calculating this workbook section, select the workbook that you want, and then select or clear the Use 1904 date system check box.

 Top of Page

Issue: I'm having problems with dates between workbooks that use different date systems

You can encounter problems when you copy and paste dates or when you create external references between workbooks based on the the two different date systems. Dates can appear four years and one day earlier or later than the date that you expect. You can encounter these problems whether you are using Microsoft Excel for Windows, Excel for the Macintosh, or both programs.

For example, if you copy the date July 5, 2007 from a workbook that uses the 1900 date system and then paste the date into a workbook that uses the 1904 date system, the date appears as July 6, 2011, which is 1462 days later. Alternatively, if you copy the date July 5, 2007 from a workbook that uses the 1904 date system and then paste the date into a workbook that uses the 1900 date system, the date appears as July 4, 2003, which is 1462 days earlier. For background information, see Learn about the two date systems.

Correct a copy and paste problem  

1. In an empty cell, enter the value 1462.
2. Select that cell, and then on the Home tab, in the Clipboard group, click Copy.
3. Select all of the cells that contain the incorrect dates.

How to select cells

4. On the Home tab, in the Clipboard group, click Paste, and then click Paste Special.

5. In the Paste Special dialog box, under Paste, click Values, and then under Operation, do one of the following:
   • To set the date as four years and one day later, click Add.
   • To set the date as four years and one day earlier, click Subtract.

Correct an external reference problem  

If you are using an external reference to a date in another workbook with a different date system, you can modify the external reference by doing one of the following:

• To set the date as four years and one day later, add 1462 to it. For example:

=[Book2]Sheet1!$A$1+1462

• To set the date as four years and one day later, subtract 1462 from it. For example:

=[Book1]Sheet1!$A$1-1462

 

 

Excel > Formula and name basics

Summing up ways to add and count Excel data

Adding 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 Office Excel provides multiple techniques that you can use to add and count data. This article provides the basics of some of the more commonly used methods and provides links to articles that go into detail on those methods.

To help you make the right choice, here's a comprehensive summary of methods and links to more information. Each section has supporting information to help you quickly decide on which technique to use and links to in-depth articles.

In this article

Adding and subtracting numbers

Simple addition and subtraction

Summing data by grouping and pivoting

Adding with one or more criteria

Adding squared and array values

Special cases (running total, unique values)

Including values from other worksheets or workbooks in a formula

Adding and subtracting date and time values

Displaying calculations and counts on the status bar

Counting number and text values

Simple counting

Counting with one or more criteria

Counting with blank values

Counting unique occurrences

Special cases (count all cells, count words)

Displaying calculations and counts on the status bar

Adding and subtracting numbers

The following sections summarize the specific tasks for adding numbers, the best Excel feature for each task, and provide links to one or more Help topics for specific examples and information.

Simple addition and subtraction

You can add and subtract numbers by using a simple formula, a button, or by using a worksheet function.

Add values in a cell by using a simple formula

If you just need a quick result, you can use Excel as a mini calculator. Do this by using the plus sign (+) arithmetic operator. For example, if you type the formula =5+10 in a cell, Excel displays 15 as the result.

For more information about how to use arithmetic operators in a formula, see the article Use Excel as your calculator.

Subtract values in a cell by using a simple formula

Do this by using the minus sign (-) arithmetic operator. For example, the formula =12-9 displays a result of 3.

For more information about how to use arithmetic operators in a formula, see the article Use Excel as your calculator.

Add values in a column or row by using a button

You can use AutoSum to quickly sum a range of numbers in a column or row. Click an empty cell below a column of numbers or to the right of a row of numbers, and then click AutoSum. Excel selects what it determines to be the most likely range of data. Click AutoSum again to accept the range that Excel selects, or select your own range and then click AutoSum.

For more information about how to use AutoSum, see the article Use Excel as your calculator.

Add values in a range by using a function

The SUM function is useful when you want to add values from different ranges or combine number values with ranges of numbers. Use the SUM function to add all the arguments (argument: The values that a function uses to perform operations or calculations. The type of argument a function uses is specific to the function. Common arguments that are used within functions include numbers, text, cell references, and names.) that you specify within the opening and closing parentheses. Each argument can be a range, a cell reference, or a numeric value.

To enter a simple formula, type =SUM in a cell, followed by an opening parenthesis. Next, type one or more numbers, cell references, or cell ranges, separated by commas. Then, type a closing parenthesis and press ENTER to display the result. You can also use your mouse to select cells that contain data that you want to sum.

For example, using the data in the preceding table, all of the following formulas use the SUM function to return the same value (17158):

• =SUM(4823,12335)
• =SUM(A2,A3)
• =SUM(A2:A3)
• =SUM(A2,12335)

The following figure shows the formula that uses the SUM function to add the value of cell A2 and 12335. Below the formula, a ScreenTip provides guidance for using the SUM function.

 Notes 

• There is no SUBTRACT function in Excel. To subtract values by using a function, use the negative values with the SUM function. For example, the formula =SUM(30,A3,-15,-B6) adds 30 and the value in cell A3, subtracts 15, and also subtracts the value in cell B6.
• You can include up to 255 numeric values or cell or range references, in any combination, as arguments in the SUM function.

For more information, see the article SUM function.

 Top of Page

Summing data by grouping and pivoting

You can use an outline or a PivotTable report to group and summarize your data.

Add values in a column by using an outline

If your data is in a list and you can logically group it by column values, you can group and summarize data by creating an outline. Creating an outline can summarize data by inserting subtotals and grand totals. Suppose you have the following sales figures, arranged by region and month.

The following illustration shows an outline with subtotals, grouped by region, and a grand total.

Use the Subtotal command (Data tab, Outline group) to create an outline, subtotals, and a grand total.

The original data included three rows of data for the East region and three rows of data for the West region (rows 2 through 7). Note that the subtotal operation inserted the East Total in row 5 between the last row of East data and the first row of West data.

If you click cell A4 and then click the Subtotal command, Excel creates an outline and inserts rows for East Total, West Total, and Grand Total, and populates the cells in the Sales column with those totals.

For more information, see the following articles:

• Outline a list of data in a worksheet
• Insert subtotals in a list of data in a worksheet
• Remove subtotals

Add values in a list or Excel table column by using a function

As a more flexible alternative to the Subtotal command, you can use the SUBTOTAL function to calculate subtotals and grand totals on lists or Excel tables that contain hidden rows or columns. In the SUBTOTAL function, you specify an argument that either includes or does not include hidden values.

 Note    The SUBTOTAL function can control whether or not hidden rows are included in the results and always ignores rows that have been filtered.

For example, suppose that you want to calculate subtotals and a grand total for data in cells C2 through C7, but you want to ignore the hidden data in row 3 and row 6. The function you use looks like the following:

=SUBTOTAL(109,C2:C7)

The first argument (109) specifies that you want to add the values in the range and ignore hidden values. To include the hidden values, you instead use 9 as the first argument.

For in-depth information, see the articles SUBTOTAL function and Total the data in an Excel table.

Sum your data and look at it from different perspectives

When a worksheet contains hundreds or even thousands of rows of similar data, it can be difficult to draw meaning from those numbers. You can build a PivotTable report in Excel from that data that shows subtotals, grand totals, and that can summarize data based on categories that you specify.

You can quickly create a PivotTable by selecting a cell in a range of data or Excel table and then, on the Insert tab, in the Tables group, clicking PivotTable.

To show the power of a PivotTable, note that in the following example, the sales data contains many rows (there are actually 40 rows of data, but the graphic shows only a portion), it isn't summarized, and it has no subtotals or grand total.

A PivotTable report based on the same data shows subtotals, grand totals, and provides a concise summary at a glance.

Creating and working with PivotTables may require some initial preparation of your data and a familiarity with some concepts. For detailed information to help you get started, see the following articles:

• PivotTable I: Get started with PivotTable reports in Excel 2007
• Create, edit, or delete a PivotTable or PivotChart formula
• Overview of values, custom calculations, and formulas in a PivotTable or PivotChart report
• Summary functions available in PivotTable reports
• Subtotal and total fields in a PivotTable report
• Custom calculations for PivotTable data fields

 Top of Page

Adding with one or more criteria

Using worksheet functions, you can apply criteria so that you add only the data that meet conditions that you specify.

Add values in a range based on a single condition by using a single function or a combination of functions

Use either the SUMIF function or nest the SUM function inside the IF function.

You can add numbers based on a single criterion by using the SUMIF function or by using a combination of the SUM and the IF functions.

For example, the formula =SUMIF(A2:A6,">20") adds only the numbers in the range A2 through A6 that are greater than 20.

 Note    The following information is a little bit technical, but definitely worth learning.

If the SUMIF function didn't exist, you could still come up with the same answer by combining the IF and the SUM functions. Combining functions in this way is also known as "nesting," because one function is used in another function.

To create a formula that mimics the SUMIF function, you enter a formula that treats the range A2 through A6 as an array, which means that the range is considered as a single entity containing five cells.

Your formula looks like this: {=SUM(IF(A2:A6>20,A2:A6))}. The "curly" braces that surround the formula indicate that this is an array formula. An array formula requires some special treatment: Instead of typing the braces yourself, you must instead press CTRL+SHIFT+ENTER  — Excel then surrounds the formula =SUM(IF(A2:A6>20,A2:A6)) with the braces. If you type the braces yourself, you won't create an array formula.

Try this!  

Copy the data from the following table into a worksheet at cell A1. After you paste the data, you'll notice that cell A10 contains a #VALUE! error. This means that you need to convert cell A10 to an array formula. Do this by pressing F2 and then pressing CTRL+SHIFT+ENTER. Excel displays the result (65) in cells A8 and A10.

How to copy the example worksheet data

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent).

For more information, see the article SUMIF function and the section "Add numbers based on one condition" in the article Add numbers.

Also, the Knowledge Base article XL: When to Use SUM(IF()) instead of CountBlank() provides additional guidance on when to use the SUMIF function.

Add values in a column based on single or multiple criteria by using a function

Use the DSUM function to add the numbers in a field (column) of records in a list or database that match conditions that you specify.

You can also 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.

For more information, see the article DSUM function.

Also see the section "Add numbers based on criteria stored in a separate range" in the article Add numbers for more information.

Add values in a range based on multiple criteria by using a wizard

You can use the Conditional Sum Wizard (Conditional Sum command, in the Solutions group on the Formulas tab) to add values that are based on multiple criteria.

If this command is not available (the Solutions group may also not be visible), install and load the Analysis ToolPak add-in.

How to load the Analysis ToolPak

1. Click the Microsoft Office Button , click Excel Options, and then click the Add-ins category.
2. In the Manage list, select Excel Add-ins, and then click Go.
3. In the Add-ins available list, select the Analysis ToolPak box, and then click OK.
4. If the wizard appears in the Inactive Application Add-ins list, select the wizard and then click Go to make it active.
5. If necessary, follow the instructions in the Setup program.

To use the Conditional Sum Wizard, select your range of data and click the Conditional Sum command (Solutions group on the Formulas tab). Follow steps 1 through 4 to complete the wizard. The following illustration shows step 2 of the wizard, in which there is one condition: The values to be summed must be greater than 100.

See the section "Add numbers based on multiple conditions with the Conditional Sum Wizard" in the article Add numbers for more information.

Add values in a range based on multiple criteria by using a single function or a combination of functions

Use the SUMIFS function. Provide the range that you want to sum, a range that contains criteria, and the criteria that are applied to the criteria range.

As an alternative, you can nest the SUM and IF functions.

For more information, see the article SUMIFS function.

See the section "Add numbers based on multiple conditions" in the article Add numbers for more information about how to use the SUM and IF functions together.

Also, see the following Knowledge Base articles for additional information:

• 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()

 Top of Page

Adding squared and array values

You can use worksheet functions to add values in two or more columns that correspond to each other. This type of operation can be as simple as determining a weighted average (see the sample that follows), or it can be a complex statistical or engineering calculation.

Add the product of corresponding values in one or more arrays

Use the SUMPRODUCT function. For example, you can calculate a student's grade point average by using the SUMPRODUCT function and dividing that result by the number of credits, as shown in the following figure.

The formula =SUMPRODUCT(A2:A6,B2:B6)/SUM(B2:B6) multiplies the grade achieved for each class by its number of credits, sums those products (61.3), divides that sum by the total credits (18), and determines the grade point average (3.41). This type of calculation is also known as a weighted average.

You can also use the SUMPRODUCT function in more advanced mathematical operations by multiplying several arrays (array: Used to build single formulas that produce multiple results or that operate on a group of arguments that are arranged in rows and columns. An array range shares a common formula; an array constant is a group of constants used as an argument.).

The SUMPRODUCT function is easy to use with arrays that have the same dimensions, but you can also use the SUM function in an array formula (array formula: A formula that performs multiple calculations on one or more sets of values, and then returns either a single result or multiple results. Array formulas are enclosed between braces { } and are entered by pressing CTRL+SHIFT+ENTER.) if you need more flexibility.

For more detailed information, see the article SUMPRODUCT function.

Add the square of each value in a range

Use the SUMSQ function to return the sum of the squares of a list of numbers or values in a range. For example, SUMSQ(2,3) returns 13.

 Note    This type of calculation is often used with statistics and complex math.

For more information, see the article SUMSQ function..

Add the sum of the square of corresponding values in two arrays

Use the SUMX2PY2 function to add the sum of the squares of corresponding values in two arrays. This is used in many statistical calculations.

 Note    This type of calculation is often used with statistics and complex math.

For more information, see the article SUMX2PY2 function.

Add the difference of the square of corresponding values in two arrays

Use the SUMX2MY2 function to return the sum of the difference of squares of corresponding values in two arrays.

 Note    This type of calculation is often used with statistics and complex math.

For more information, see the article SUMX2MY2 function.

Add the square of the difference of corresponding values in two arrays

Use the SUMXMY2 function to return the sum of squares of differences of corresponding values in two arrays.

 Note    This type of calculation is often used with statistics and complex math.

For more information, see the article SUMXMY2 function.

 Top of Page

Special cases (running total, unique values)

You can use worksheet functions to create totals that update as you add more data to a range or table, or to add only the unique values that are found in a range or table.

Create a running total by using a function

Use the SUM function. For example, create column headings in a worksheet named Deposits, Withdrawals, and Balance.

1. In the first cell under the Balance column, enter your beginning balance.
2. In the cell directly below, enter a formula that adds the value from the Deposits column in the same row and subtracts the value from the Withdrawals column (=SUM(C2,A3,-B3).
3. Drag this formula down to any new rows that you add (in this case, drag it to row 4). This is shown in the following example.

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

You can use a running balance as you would use a check register to watch values of items in cells increase or decrease as you enter new items and values over time.

For more information, see the article Calculate a running balance.

Add unique values in a range by using a mega-formula

In a column, enter a list of values that contains duplicates, and then use a combination of the SUM, IF, and FREQUENCY functions to add only the unique values that are found in that range.

For detailed information about performing this type of operation, see the "Add unique values" section in the article Add numbers.

 Top of Page

Including values from other worksheets or workbooks in a formula

You can add or subtract cells or ranges of data from other worksheets or workbooks in a formula by including a reference to them. To refer to a cell or range in another worksheet or workbook, use instructions in the following table.

 Top of Page

Adding and subtracting date and time values

You can use date and time functions and even simple addition or subtraction to calculate elapsed time, estimate completion dates for projects, and more. The following table shows some formulas that calculate elapsed times and days. Note that if you subtract a date or time that results in a negative value, Excel displays ### characters in the cell that contains the formula.

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

Working with date and time values can produce unexpected results, so proceed carefully. See the following articles for detailed information about using formulas and functions to work with dates and times.

• Add or subtract dates
• Add or subtract units of time
• Calculate the difference between two dates
• Calculate the difference between two times
• Count days before a date

 Top of Page

Displaying calculations and counts on the status bar

When one or more cells are selected, information about the data in those cells is displayed on the Excel status bar. For example, if four cells on your worksheet are selected, and they contain the values 2, 3, a text string (such as "cloud"), and 4, all of the following values can be displayed on the status bar at the same time: Average, Count, Numerical Count, Min, Max, and Sum. Right-click the status bar to show or hide any or all of these values. These values are shown in the illustration that follows.

 Note    In earlier versions of Excel, these same values can be displayed on the status bar, but only one value at a time.

 Top of Page

Counting number and text values

The following sections summarize the specific tasks for counting number and text values, the best Excel feature for each task, and provide links to one or more Help topics for specific examples and information.

Simple counting

You can use the AutoSum command or a function to count the number of values in a range or table. In addition, Excel can also display, on the status bar, the count of the number of selected cells on a worksheet.

Count cells in a column or row by using a built-in command

Use the Count Numbers AutoSum command. Select a cell below or to the right of the numbers that you want to count, and click the arrow next to the AutoSum command. Then, click Count Numbers.

You can use AutoSum for more than just summing; you can use it to count the numbers in a range and also to find the minimum and maximum values.

For more information, see the section "Count cells that contain numbers in a contiguous row or column" in the article Count cells that contain numbers.

Count cells in a range by using a function

Use the COUNT function to count the number of numeric values in a range. For example, the range A2:A5, as shown in the following table, contains three numbers and one text value. You use the COUNT function like this: =COUNT(A2:A5). The result is 3; the number of numeric values found in the range.

The COUNT function is useful when you want to count values from different ranges or combine literal values with ranges of values.

For more information, see the section "Count cells that contain numbers that are not in a contiguous row or column" in the article Count cells that contain numbers, and the article COUNT function.

Count cells in a column by using an outline

Use the Subtotal command (Data tab, Outline group) to group and summarize a column of data.

If your data is in a list and you can logically group it by column values, you can create an outline to group and summarize it.

For more information, see the following articles:

• Outline a list of data in a worksheet
• Insert subtotals in a list of data in a worksheet
• Remove subtotals
• Calculating subtotals and working with levels in Excel

Count cells in a column or row by using a PivotTable

Create a PivotTable report that summarizes your data and helps you perform analysis by letting you choose the categories on which you want to view your data.

You can quickly create a PivotTable by selecting a cell in a range of data or Excel table and then, on the Insert tab, in the Tables group, clicking PivotTable.

Creating and working with PivotTables may require some initial preparation of your data and a familiarity with some concepts.

For detailed information to help you get started, see the following articles:

• Create, edit, or delete a PivotTable or PivotChart formula
• Summary functions available in PivotTable reports
• Subtotal and total fields in a PivotTable report
• Custom calculations for PivotTable data fields
•  

Count cells in a list or Excel table column by using a function

Use the SUBTOTAL function to count the number of values in an Excel table or range of cells.

The SUBTOTAL function can control whether or not hidden rows are included in the results and always ignores rows that have been filtered.

For more information, see the articles SUBTOTAL function and Total the data in an Excel table.

 Top of Page

Counting with one or more criteria

You can count the number of cells in a range that meet criteria that you specify by using a number of worksheet functions.

Count cells in a range based on a single criterion

Use the COUNTIF function to count the number of cells that meet one condition.

For more information, see the articles Count numbers greater than or less than a number and COUNTIF function.

Count cells in a column based on single or multiple criteria

To match conditions that you specify, use the DCOUNT database function.

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.

For more information, see the article DCOUNT function.

Count cells in a range based on multiple criteria by using a single function or a combination of functions

Use the COUNTIFS function or a combination of the COUNT and IF functions.

For more information, see the articles Count how often a value occurs and COUNTIFS function.

 Top of Page

Counting with blank values

You can count cells that either contain data or are blank by using worksheet functions.

Count nonblank cells in a range by using a function

Use the COUNTA function to count only cells in a range that contain values.

When you count 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.

For more information, see the articles Count nonblank cells and COUNTA function.

Count nonblank cells in a list by using a function

Use the DCOUNTA function to count nonblank cells in a column of records in a list or database that match conditions that you specify.

For more information, see the article DCOUNTA function.

Count blank cells in a contiguous range by using a function

Use the COUNTBLANK function to return the number of blank cells in a contiguous range (cells are contiguous if they are all connected in an unbroken sequence). If a cell contains a formula that returns empty text (""), that cell is counted.

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

For more information, see the article COUNTBLANK function.

Count blank cells in a discontiguous range by using a combination of functions

Use a combination of the SUM function and the IF function. In general, you do this by using the IF function in an array formula to determine whether each referenced cell contains a value, and then summing the number of FALSE values returned by the formula.

See the Knowledge Base article XL: When to Use SUM(IF()) instead of CountBlank() for additional information.

 Top of Page

Counting unique occurrences

You can count unique values in a range by using a PivotTable report, by using the COUNTIF function, by using a combination of functions, or by using the Advanced Filter dialog box.

Count unique values in a range by using a PivotTable report

You can use a PivotTable report to display totals and count the occurrences of unique values.

For more information, see the section "Count how often multiple values occur by using a PivotTable report" in the article Count how often a value occurs.

Count unique values in a range based on a single criterion by using a function

Use the COUNTIF function to count the number of times a value occurs in a range.

For more information, see the section "Count how often a single value occurs in a range" in the article Count how often a value occurs.

Count unique values in a range based on multiple criteria by using a combination of functions in an array formula

Use the SUM function with the IF function. In general, you do this by using the IF function in an array formula to determine whether criteria that is composed of multiple conditions is met, and then summing the number of TRUE values returned by the formula.

For more information, see the section "Count how often multiple text or number values occur by using functions" in the article Count how often a value occurs.

See the following Knowledge Base articles for additional tips:

• 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

Count the number of unique values in a list column by using Advanced Filter

Use the Advanced Filter dialog box to extract the unique values from a column of data and paste them to a new location. Then you can use the ROWS function to count the number of items in the new range.

The simplest way to do this is by using the Advanced command (Data tab, Sort & Filter group).

The following figure shows how you use the Advanced Filter to copy only the unique records to a new location on the worksheet.

In the following figure, column C contains the five unique values from the range in column A.

For more information, see the section "Count the number of unique values by using a filter" in the article Count unique values among duplicates.

Count the number of unique values in a range with one or more criteria by using a mega-formula

Use various combinations of the IF, SUM, FREQUENCY, MATCH, and LEN functions.

For more information, see the section "Count the number of unique values by using functions" in the article Count unique values among duplicates.

Also, see the article How to determine the number of unique items in a list.

 Top of Page

Special cases (count all cells, count words)

You can count the number of cells or the number of words in a range by using various combinations of worksheet functions.

Count the total number of cells in a range by using functions

Suppose you want to determine the size of a large worksheet to decide whether to use manual or automatic calculation in your workbook. To count all the cells in a range, use a formula that multiplies the return values from the ROWS and COLUMNS functions.

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

For more information, see the article Count all of the cells in a range.

Count words in a range by using a mega-formula

Use a combination of the SUMIF, LEN, TRIM, and SUBSTITUTE functions in an array formula. In brief, to find the total number of words in the specified range, the array formula counts the words in each cell of that range, and the SUM function then sums the number of words in each cell to find the total.

For more information and a detailed example, see the article Count the number of words in a cell or range.

 Top of Page

Displaying calculations and counts on the status bar

When one or more cells are selected, information about the data in those cells is displayed on the Excel status bar. For example, if four cells on your worksheet are selected, and they contain the values 2, 3, a text string (such as "cloud"), and 4, all of the following values can be displayed on the status bar at the same time: Average, Count, Numerical Count, Min, Max, and Sum. Right-click the status bar to show or hide any or all of these values. These values are shown in the illustration that follows.

 Note    In earlier versions of Excel, these same values can be displayed on the status bar, but only one value at a time.

 Top of Page