SheetsdrillSUM and AVERAGE
SUM(value1, [value2, ...]) adds every number in the ranges you give it and AVERAGE returns their arithmetic mean, both skipping text and empty cells. A share of total is a value divided by SUM with the total locked with $ signs, such as =B2/SUM($B$2:$B$13).
Syntax
=SUM(value1, [value2, ...]) and =AVERAGE(value1, [value2, ...])| Part | What it is |
|---|---|
value1 | A range, a cell or a number, for example B2:B13. |
value2 | Optional further ranges or numbers, added to the same total. |
Examples
=SUM(B2:B13)Result: The total of the twelve monthly figures
=AVERAGE(C2:C100)Result: Average order value when column C holds order amounts
Or =SUM(C2:C100)/COUNT(C2:C100), the same number.
=B2/SUM($B$2:$B$13)Result: Row 2's share of the total, for example 0.083; format as a percentage
The $ signs keep the total fixed when the formula is copied down.
Common mistakes
- A SUM of 0 over visible numbers means they are text; check for a leading apostrophe or left alignment and use VALUE or Format > Number.
- AVERAGE ignores blanks but counts zeros, so a column padded with 0 for missing months pulls the average down; use AVERAGEIF(range, ">0").
- Dragging a share-of-total formula without $ moves the total range with it; lock it with $B$2:$B$13.
Practice SUM and AVERAGE
These drills check your answer on live data, free, no account needed.
- Total ad spendSix marketing channels and their spend for the month are listed. Click B8, type a formula that adds up all six, press Enter, then press Enter again (or choose Check answer).
- Average order valueEight orders came in today. Put the average order amount in E2.
- Share of total salesB6 holds the total of all regions. Work out each region's share of that total in C2 (North) and C3 (South). Lock the total so the formula could be filled down.
Questions
How do I sum a whole column?
=SUM(B:B) adds every number in column B; keep the formula outside column B to avoid a circular reference.
How do I average a percentage column?
AVERAGE works on the decimals behind the formatting; if the percentages are weights of different sizes, use SUMPRODUCT divided by SUM instead.
Related
- SUMIFHow SUMIF works in Google Sheets: add up values where another column matches text, a number, a date or a wildcard, with examples and the mistakes that give 0.
- AVERAGEIFHow AVERAGEIF and AVERAGEIFS in Google Sheets average only the cells that meet one or more conditions, with examples for a team, a threshold and dates.
- ROUNDHow ROUND, ROUNDUP, ROUNDDOWN and MROUND work in Google Sheets, with examples for two decimals, thousands and nearest 0.05, and why formatting does not round.
- SUMPRODUCTHow SUMPRODUCT in Google Sheets multiplies two ranges row by row and adds the results, and how it works as a conditional sum with OR logic, with examples.