← Data Analytics from Scratch: SQL, Spreadsheets and Metrics
Lesson
References and core functions: SUM, AVERAGE, COUNT
Learner can write SUM, AVERAGE and COUNT/COUNTA formulas over a correct cell range and avoid off-by-one range errors.
Cell references, ranges and core aggregation functions
Cell references, ranges and core aggregation functions
A spreadsheet cell is identified by a column letter and a row number: A1 is column A, row 1. A range like A1:A5 means every cell from A1 down to A5 — five cells in total. Ranges are the building blocks of every formula. A common beginner mistake is including a header row (which contains text, not a number) or accidentally leaving out the last data row — both break your result.
The three most essential aggregation functions are SUM, AVERAGE and COUNT. SUM adds all the numbers in a range: =SUM(B2:B6) totals cells B2 through B6. AVERAGE divides that total by the number of numeric cells: =AVERAGE(B2:B6). COUNT counts only numeric cells in a range and ignores text and blanks — so if you accidentally include a header like "Sales" it will not be counted. COUNTA counts everything that is not empty, including text; use it when you need to count rows regardless of content type.
Let's work through a verified example. A sales column contains five values: 120, 80, 200, 80, 150 placed in B2:B6. The header "Sales" is in B1. If you write =SUM(B2:B6) you get 630. =AVERAGE(B2:B6) gives 126 (630 ÷ 5). =COUNT(B2:B6) returns 5 because all five cells are numeric. If you mistakenly extend the range to B1:B6 to include the header, SUM still returns 630 (text is ignored by SUM). AVERAGE also ignores text, so =AVERAGE(B1:B6) still returns 126 (630 divided by the 5 numeric cells). The real danger is silent: if the header cell B1 were ever replaced with a number, both SUM and AVERAGE would change without any error. The safe rule: always start your range at the first data row (B2), never at the header.
Both Microsoft Excel and Google Sheets share identical English function names: SUM, AVERAGE, COUNT, COUNTA. This means any formula you learn here works in both tools.
Lesson notes
Cell references, ranges and core aggregation functions
A spreadsheet cell is identified by a column letter and a row number: A1 is column A, row 1. A range like A1:A5 means every cell from A1 down to A5 — five cells in total. Ranges are the building blocks of every formula. A common beginner mistake is including a header row (which contains text, not a number) or accidentally leaving out the last data row — both break your result.
The three most essential aggregation functions are SUM, AVERAGE and COUNT. SUM adds all the numbers in a range: =SUM(B2:B6) totals cells B2 through B6. AVERAGE divides that total by the number of numeric cells: =AVERAGE(B2:B6). COUNT counts only numeric cells in a range and ignores text and blanks — so if you accidentally include a header like "Sales" it will not be counted. COUNTA counts everything that is not empty, including text; use it when you need to count rows regardless of content type.
Let's work through a verified example. A sales column contains five values: 120, 80, 200, 80, 150 placed in B2:B6. The header "Sales" is in B1. If you write =SUM(B2:B6) you get 630. =AVERAGE(B2:B6) gives 126 (630 ÷ 5). =COUNT(B2:B6) returns 5 because all five cells are numeric. If you mistakenly extend the range to B1:B6 to include the header, SUM still returns 630 (text is ignored by SUM). AVERAGE also ignores text, so =AVERAGE(B1:B6) still returns 126 (630 divided by the 5 numeric cells). The real danger is silent: if the header cell B1 were ever replaced with a number, both SUM and AVERAGE would change without any error. The safe rule: always start your range at the first data row (B2), never at the header.
Both Microsoft Excel and Google Sheets share identical English function names: SUM, AVERAGE, COUNT, COUNTA. This means any formula you learn here works in both tools.