Excel SUM and AVERAGE Functions

SUM and AVERAGE are the two most-used Excel functions for totaling a range of numbers and finding their typical value.

Adding Up Numbers with SUM

SUM adds together every number in the range or list you give it. Its syntax is SUM(number1, [number2], ...), where each argument can be a single cell, a range like B2:B10, or a typed-in number, and you can mix several of them separated by commas.

Example

=SUM(B2:B10)

SUM automatically skips over empty cells and text, so a stray label mixed into a numeric range will not break the total, it simply gets ignored.

Finding the Typical Value with AVERAGE

AVERAGE computes the arithmetic mean of a range: it adds up every number and divides by how many numbers it found. Its syntax mirrors SUM: AVERAGE(number1, [number2], ...).

Example

=AVERAGE(B2:B10)
Note: AVERAGE ignores blank cells when counting how many values to divide by, but a cell containing 0 is counted as a real value. Mixing up "blank" and "zero" is a common cause of an average looking lower than expected.

Related Aggregate Functions

FunctionPurpose
SUMAdds every number in a range.
AVERAGECalculates the arithmetic mean of a range.
COUNTCounts how many cells in a range contain numbers.
MAXReturns the largest number in a range.
MINReturns the smallest number in a range.

Both SUM and AVERAGE accept multiple ranges at once, which is handy when the numbers you need are not next to each other, for example =SUM(B2:B10, D2:D10) adds two separate columns in a single formula.

Note: Select a range of numbers and look at the status bar along the bottom of the Excel window: it automatically shows the Sum, Average, and Count of the selection without writing any formula at all.

Keep Going

Excel Interview QuestionsPractice real questions asked for ExcelData Analyst SalarySee what Data Analyst roles pay in the marketAuto Apply for JobsApply to open roles related to Excel and tweak your resume