SUM, AVERAGE, COUNT, MIN and MAX
Six students, five scores and the word absent: AVERAGE divides by 4 here, not by 6, because it skips text and empty cells but counts the 0.
A score sheet with one absence and one missing mark
Picture the score sheet in the lab below. Ana has 82, Chloe 91, Eli 0 and Fay 77. Ben was absent, so someone typed the word absent. Dev's test hasn't been marked yet, so his cell is empty. What is the class average? Count the cells and you might divide by 6. AVERAGE divides by 4 and gets 62.5, the same answer as someone with a calculator who skips Ben and Dev.
The rule behind that answer is short. SUM adds, AVERAGE gives the arithmetic mean, MIN and MAX give the smallest and largest number, and COUNT says how many numbers there are. All five take a range such as B2:B7, or several ranges separated by commas, and all five read only the numbers in a range. Microsoft's AVERAGE page puts it plainly: text, logical values and empty cells in a range are ignored, but cells with the value zero are included. Google's help says the same for Sheets. So Ben's absent and Dev's blank drop out, and Eli's 0 stays in.
This is why a blank and a zero are not the same thing. A student who scored 0 pulls the average down. A student with no mark yet leaves it untouched. Microsoft's own example averages 10, 7, 9, 27 and 2 to get 11; add 5 as a second argument, =AVERAGE(A2:A6, 5), and it becomes 10, because 60 ÷ 6 = 10. And a range with no numbers at all? AVERAGE has nothing to divide by and returns #DIV/0!.
Counting numbers, or counting filled cells
COUNT counts numbers only. Microsoft's example column holds a date, 19, 22.24, TRUE and #DIV/0!, and =COUNT(A2:A6) gives 3. The date counts, because a date is stored as a number. TRUE and the error do not. COUNTA counts every cell that isn't empty, whatever it holds, so the same column gives 5. Google's COUNTA help adds a trap worth knowing: zero-length text and spaces count too, so a cell whose formula returns "" looks blank and is still counted.
| Function | What it returns | Text in the range | Empty cells |
|---|---|---|---|
| SUM | total | ignored | ignored |
| AVERAGE | total ÷ count of numbers | ignored | ignored |
| COUNT | how many numbers | not counted | not counted |
| COUNTA | how many non-empty cells | counted | not counted |
| MIN / MAX | smallest / largest number | ignored | ignored |
Is the mean always the right middle value? No. Microsoft's AVERAGE page uses 2, 3, 3, 5, 7 and 10: the mean is 5, the median (the middle value) is 4, and the mode (the most frequent value) is 3. Both programs have MEDIAN and MODE for these. When one extreme number sits in the data, like a single 0 among scores in the 80s, the median describes a typical student better than the mean does.
The two programs differ on one small point, the number of arguments a function accepts. Excel's limits page gives 255. Google's help for AVERAGE and COUNTA says Sheets takes any number. With ranges you will never notice either limit. The handling of numbers, text and blanks described above is the same in both, so a sheet built in one gives the same answers in the other.
MIN and MAX follow the same rule. With no numbers in the range, both return 0 instead of an error, and that 0 looks like a real result. If a price list reports a lowest price of 0, check whether there really is a free item, or whether the column simply holds no numbers.
Drag to turn. Scroll or pinch to zoom.
- Typing 0 for a test that hasn't been marked yet. AVERAGE counts the 0, so the average drops. Leave the cell empty until there is a real mark.
- Using COUNT to count names or Yes answers. COUNT sees only numbers, so a column of text gives 0. COUNTA counts filled cells.
- Trusting COUNTA on a column of formulas that return "". Google's help says zero-length text is counted, so cells that look blank still add to the total.
- Adding cells with + when one of them holds text. With absent in B3, =B2+B3 gives #VALUE!, while =SUM(B2:B3) skips the text. Microsoft's #VALUE! page suggests a function such as SUM for this reason.
- B2:B7 holds 82, absent, 91, (empty), 0, 77. What does =AVERAGE(B2:B7) return?
- Same cells. What does =COUNT(B2:B7) return?
- Same cells. What does =COUNTA(B2:B7) return?
- Same cells. What does =MIN(B2:B7) return?
- A2:A6 holds 10, 7, 9, 27, 2. What does =AVERAGE(A2:A6) return?
- And =AVERAGE(A2:A6, 5)?
- A column holds a date, 19, 22.24, TRUE and #DIV/0!. What does COUNT return?
- B2:B4 holds only text. What does =AVERAGE(B2:B4) return?
- #DIV/0!
- 0
- #VALUE!
- #N/A
- What is the median of 2, 3, 3, 5, 7, 10?
- Why does a blank score not lower the AVERAGE but a 0 does?
- Empty cells are ignored; zeros are numbers and are included
- Zeros are text
- Blanks count as the average
- AVERAGE rounds blanks up
- How many arguments can an Excel function take?
- SUM, AVERAGE, MIN, MAX and COUNT read only the numbers in a range.
- Empty cells are ignored; zeros are counted.
- COUNT counts numbers; COUNTA counts every non-empty cell, even "".
- AVERAGE of no numbers is #DIV/0!.
Sources for this course (19)
- Microsoft Support, "Switch between relative, absolute, and mixed references" (support.microsoft.com/en-us/office/dfec08cd-ae65-4f56-839e-5f0d8d0baca9: $ before column and row, F4 to switch, copying two down and two right: $A$1 stays, A$1 to C$1, $A1 to $A3, A1 to C3)
- Microsoft Support, "AVERAGE function" (support.microsoft.com/en-us/office/047bac88-d466-426c-a32b-8f33eb960cf6: text, logical values and empty cells in a reference ignored, zeros included; 10, 7, 9, 27, 2 gives 11, with 5 gives 10; mean 5, median 4, mode 3 for 2 3 3 5 7 10)
- Microsoft Support, "COUNT function" (support.microsoft.com/en-us/office/a59cd7fc-b623-4d93-87a4-d23bf411294c: only numbers counted, dates count, errors and text do not)
- Microsoft Support, "ROUND function" (support.microsoft.com/en-us/office/c018c5d8-40fb-4053-90b1-b3e7f61a213c: num_digits required; 2.15 to 2.2, -1.475 to -1.48, 626.3 to 1000, 1.98 to 0, -50.55 to -100)
- Microsoft Support, "IF function" (support.microsoft.com/en-us/office/69aed7c9-4e8a-4755-a9bc-aa8bbff73be2: value_if_false optional, text in quotes except TRUE and FALSE, 0 and #NAME? problems)
- Microsoft Support, "VLOOKUP function" (support.microsoft.com/en-us/office/0bbc8083-26fe-4963-8ab8-93a18ad188a1: range_lookup TRUE by default, sorted first column, #N/A, #REF!, #VALUE!, absolute table range, wildcards with FALSE)
- Microsoft Support, "XLOOKUP function" (support.microsoft.com/en-us/office/b7fd680e-6d10-43e6-84f9-88eae8bf5929: not in Excel 2016 and Excel 2019, exact match by default)
- Microsoft Support, "How to correct a #VALUE! error" (support.microsoft.com/en-us/office/15e1b616-fbf2-4147-9c0b-0a11a20e409e: + and * may not calculate cells with text, use SUM instead, IFERROR hides all errors)
- Microsoft Support, "Excel specifications and limits" (15 digits, 64 nested levels, 255 arguments)
- Google Docs Editors Help, "VLOOKUP" (support.google.com/docs/answer/3093318: is_sorted TRUE by default, FALSE recommended, search down to a larger value, " Apple" and "Apple " differ, wildcards, IFNA, absolute range)
- Google Docs Editors Help, "XLOOKUP" (support.google.com/docs/answer/12405947)
- Google Docs Editors Help, "ROUND" (support.google.com/docs/answer/3093440: places optional, 0 by default, rounding by magnitude)
- Google Docs Editors Help, "IF" (support.google.com/docs/answer/3093364: order of the answers is the most common problem, IFS)
- Google Docs Editors Help, "AVERAGE" (support.google.com/docs/answer/3093615: text ignored, any number of arguments)
- Google Docs Editors Help, "COUNTA" (support.google.com/docs/answer/3093991: zero-length strings and spaces count)
- Google Docs Editors Help, "Keyboard shortcuts for Google Sheets" (support.google.com/docs/answer/181110: F4 and Fn+F4 while entering a formula, F4 as Redo)
- Pryor Learning and Exceljet (F4 cycle A1, $A$1, A$1, $A1; = comparison not case-sensitive)
- Ablebits (IF without value_if_false returns FALSE)
- Spreadsheet Class (Sheets circular dependency shows #REF!)
Checked: 2026-10-19