Excel SUM and AVERAGE: Formulas, Examples, and Tips

Formulas & functions · Updated November 18, 2026

In Excel, use SUM to add numbers and AVERAGE to find their arithmetic mean. With values in cells B2:B6, enter =SUM(B2:B6) to total them or =AVERAGE(B2:B6) to calculate their average; both formulas update when the included numbers change.

What SUM and AVERAGE calculate

SUM returns the total of numeric values in the cells or ranges you specify. It is useful for adding sales, hours, expenses, quantities, or other measurements. AVERAGE returns the arithmetic mean: Excel adds the numeric values and divides their total by the number of numeric values. The two functions often appear together in reports, but they answer different questions. A total describes the combined amount; an average describes a typical value by giving each included number equal weight.

For example, suppose B2:B6 contains 12, 8, 15, 10, and 5. The sum is 50, and the average is 10 because there are five numeric values. Averages can be useful for comparing groups of different sizes, but they can hide variation. If one day’s sales are unusually high, the mean may not describe most days particularly well. Looking at the underlying values, or using the median when appropriate, can provide useful context.

SUM and AVERAGE are built-in Excel functions. You do not need to install an add-in or turn on a special calculation option to use them in Microsoft 365 for Windows.

Basic formulas and a worked example

For this example, enter 12, 8, 15, 10, and 5 in cells B2:B6. The following formulas use that same layout:

  • To add the five numbers in B2:B6, enter =SUM(B2:B6). The result is 50.
  • To average the five numbers in B2:B6, enter =AVERAGE(B2:B6). The result is 10.

To enter either formula, select an empty cell, type the equals sign and function, include the range in parentheses, and press Enter. You can type the cell references, or select the cells with the mouse while entering the formula. The colon in a reference such as B2:B6 means “from B2 through B6,” including both endpoints and every cell between them.

When you edit a number in the range, Excel recalculates the result automatically if calculation is set to Automatic, which is the usual default. If a result seems stale, check Formulas > Calculation Options. In a workbook set to Manual, press F9 to recalculate formulas.

Using AutoSum for a quick total or average

AutoSum can insert a SUM formula without requiring you to type the function. Select the empty cell immediately below a column of numbers, or to the right of a row, and choose Home > AutoSum in the Editing group. Excel proposes a range; check that the highlighted cells are the ones you intend to include, then press Enter. You can also choose Formulas > AutoSum.

On Windows, Alt+= is the keyboard shortcut for AutoSum. AutoSum is designed to recognize a nearby block of numbers, but it can miss cells if the range is interrupted by a blank row or other content. Always inspect the suggested range before accepting it. To calculate an average instead of a sum, select the AutoSum dropdown and choose Average, then verify the proposed range and press Enter.

The ribbon names are for Excel for Microsoft 365 on Windows. Excel for the web has an AutoSum option on the Home tab, though the ribbon layout can vary with window size. On Mac, use the AutoSum command on the Home or Formulas tab; if a keyboard shortcut differs in your Excel release or keyboard setup, the ribbon command is a reliable alternative.

Adding separate ranges and applying conditions

A SUM formula can include more than one range. For example, suppose B2:B4 contains 10, 20, and 30, while D2:D4 contains 1, 2, and 3. With those values in those cells, use =SUM(B2:B4,D2:D4) to add both ranges. The result is 66. In a typical U.S. regional setting, commas separate function arguments; some regional settings use semicolons instead. If Excel rejects a formula copied from another locale, its argument separator may be the reason.

When a total should include only rows meeting a condition, SUMIF is more suitable than adding a range and manually excluding values. Suppose A2:A6 contains East, East, West, East, and West, and B2:B6 contains 120, 90, 80, 110, and 100, respectively. With regions in A2:A6 and amounts in B2:B6, enter =SUMIF(A2:A6,"East",B2:B6). The result is 320, the total for the three East rows. The arguments specify the cells to check, the condition, and the cells to add.

For more than one condition, SUMIFS can add values only when all specified criteria are met. For instance, if A2:A6 contains regions, B2:B6 contains product names, and C2:C6 contains sales amounts, use =SUMIFS(C2:C6,A2:A6,"East",B2:B6,"Desk") to total sales for rows where the region is East and the product is Desk. The result depends on the actual rows in that layout. SUMIFS places the sum range first, followed by pairs of criteria ranges and criteria.

Choosing the right kind of average

AVERAGE calculates a simple arithmetic mean, giving each numeric entry the same influence. If a person worked 4, 6, and 8 hours on three days, the average is 6 hours per day. That calculation answers a question about the average day, not the average hour across a differently weighted set of shifts or employees. Choose the range to match the unit and question you are trying to answer.

AVERAGEIF calculates a mean for values that meet one condition. Using the East-region example above, with regions in A2:A6 and sales amounts in B2:B6, enter =AVERAGEIF(A2:A6,"East",B2:B6). The East amounts are 120, 90, and 110, so the result is 106.6666667, displayed as 106.67 if the result cell is formatted to two decimal places. Formatting changes how a value appears, not the underlying calculation.

AVERAGEIFS supports multiple conditions. With regions in A2:A6, product names in B2:B6, and amounts in C2:C6, use =AVERAGEIFS(C2:C6,A2:A6,"East",B2:B6,"Desk") to average amounts for rows meeting both conditions. Make sure the average range and each criteria range cover corresponding rows. If no row meets the criteria, Excel returns a division-by-zero error because there are no qualifying numbers to average.

Blanks, zeros, text, and percentages

In a cell range, AVERAGE ignores blank cells and text entries, but it includes numeric zeroes. This distinction matters: a blank can mean “no observation,” while zero can mean a real measured result. If B2:B4 contains 10, a blank, and 0, the average is 5, because Excel averages the two numeric values, 10 and 0. Do not replace missing data with zero unless zero is genuinely the intended value.

SUM likewise adds numeric values in a referenced range and does not add ordinary text or blanks. A number stored as text may not be included as a number, even if it looks like one on screen. If a cell contains a green error indicator or behaves unexpectedly, check whether the value is stored as text and convert it to a number where appropriate. Do not convert labels or codes that only look numeric, such as an ID with leading zeroes.

Percentages are stored as numeric values in Excel. If C2:C4 contains 10%, 20%, and 30%, the average is 20%. The cell format determines whether Excel displays the result as 20% or as its underlying decimal value, 0.2. When averaging percentages, confirm that each value has the same meaning and that a simple mean is appropriate; rates based on different-sized groups may require a weighted calculation instead.

Common errors and how to fix them

A result of 0 from SUM can mean that the selected range contains no numeric values, or that numbers are stored as text. A surprising average may mean that the range includes a zero, excludes a row, or includes values that should not be compared. Click the result cell and inspect the formula bar and colored range outline to see which cells the formula references.

  • #DIV/0!: AVERAGEIFS can return this when no numbers meet the criteria. Check spelling, spaces, criteria ranges, and whether matching rows contain numeric values.
  • Unexpected total: Confirm that the range includes every intended row and does not include a subtotal that would double-count values.
  • Wrong average: Check whether blanks, zeros, or text represent missing data or actual observations, and confirm the selected group.
  • Formula displayed as text: The cell may be formatted as Text, or the entry may begin with an apostrophe. Change the format to General, then re-enter the formula.
  • Formula not updating: Check Formulas > Calculation Options for Manual calculation, or review whether the referenced cells are actually inside the range.

For formulas based on a changing list, consider converting the data to an Excel table with Insert > Table. Tables can expand as rows are added, and their structured references can make a calculation easier to read. For a small, fixed range, ordinary cell references are usually simpler. Whichever approach you use, check that the formula covers the data and not headings or unrelated values.

Frequently Asked Questions

What is the Excel formula for sum and average?
Use SUM to add a range and AVERAGE to find its arithmetic mean. With numbers in B2:B6, the formulas are =SUM(B2:B6) and =AVERAGE(B2:B6).

How do I sum a column in Excel?
Select the empty cell below the numbers and choose Home > AutoSum, then check the proposed range and press Enter. You can also type a SUM formula that refers to the column range you want to total.

Does AVERAGE count blank cells as zero?
No. AVERAGE ignores blank cells in a range, but it includes numeric zeroes. A zero can therefore lower the result, while a blank does not contribute a value or increase the count.

How do I average only numbers that meet a condition?
Use AVERAGEIF for one condition or AVERAGEIFS for multiple conditions. Set the average range to the cells containing the values to average and make sure the criteria ranges line up row by row.

Why does my Excel average show #DIV/0!?
This commonly happens when an AVERAGEIFS formula finds no numeric values that meet its conditions. Check that the criteria match the data and that qualifying cells contain numbers; an empty or text-only match cannot produce a numeric average.

Choosing a reliable formula

Use SUM for a straightforward total, AVERAGE for a simple mean, and the IF versions when rows need to meet conditions. Before relying on a result, verify the range, consider how blanks and zeros should be treated, and check whether a plain average fits the question. These quick checks help prevent an accurate formula from producing a misleading answer because it was applied to the wrong data.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.