Unit 2 of 12
SUM and friends
Add, average, and count whole columns.
Functions in this unit: SUM, AVERAGE, MAX, MIN, COUNTA, COUNT, COUNTBLANK
Key ideas
SUM adds a whole range
=SUM(C2:C7)One formula instead of typing C2+C3+C4. Text cells in the range are skipped.
Lesson 1: SUM
What does =SUM(A1:A3) do?
- Adds A1, A2 and A3
- Adds A1 and A3 only
- Counts cells in A1:A3
- Multiplies A1 by A3
Show answer
Adds A1, A2 and A3. A colon means every cell from the first to the last.
Total all units sold in C8.
A B C D 1 Product Region Units Price 2 Notebook East 120 3.5 3 Pen West 340 1.25 4 Stapler East 45 12 5 Binder North 80 6.75 6 Marker West 210 2.4 7 Folder North 150 1.9 8 Total Hint: =SUM( then select C2 through C7.
Show answer
=SUM(C2:C7)in C8. SUM is the most used function in Excel.Build a formula that adds up D2 through D7
Show answer
=SUM(D2:D7). A range is written as first cell, colon, last cell.What will B1 show?
A B 1 5 2 n/a 3 10 B1 contains
=SUM(A1:A3)Show answer
15. SUM quietly skips text inside a range.
Add Maya's three test scores in E2.
A B C D E 1 Student Test 1 Test 2 Test 3 Total 2 Maya 92 88 95 3 Leo 67 74 70 4 Priya 85 91 78 5 Sam 58 62 71 6 Nora 79 83 88 7 Eli 96 90 99 Hint: Ranges can go across a row too: B2:D2.
Show answer
=SUM(B2:D2)in E2. Ranges work horizontally as well as vertically.
Lesson 2: AVERAGE, MIN, MAX
Find the class average for Test 1 in B8.
A B C D 1 Student Test 1 Test 2 Test 3 2 Maya 92 88 95 3 Leo 67 74 70 4 Priya 85 91 78 5 Sam 58 62 71 6 Nora 79 83 88 7 Eli 96 90 99 8 Average Hint: =AVERAGE(B2:B7)
Show answer
=AVERAGE(B2:B7)in B8. AVERAGE adds the numbers and divides by how many there are.What was the highest Test 2 score? Put it in C8.
A B C D 1 Student Test 1 Test 2 Test 3 2 Maya 92 88 95 3 Leo 67 74 70 4 Priya 85 91 78 5 Sam 58 62 71 6 Nora 79 83 88 7 Eli 96 90 99 8 Best Hint: Use MAX on column C.
Show answer
=MAX(C2:C7)in C8. MAX returns the largest number in a range.Find the lowest price in D8.
A B C D 1 Product Region Units Price 2 Notebook East 120 3.5 3 Pen West 340 1.25 4 Stapler East 45 12 5 Binder North 80 6.75 6 Marker West 210 2.4 7 Folder North 150 1.9 8 Lowest Hint: Use MIN on the Price column.
Show answer
=MIN(D2:D7)in D8. MIN returns the smallest number in a range.Match each function to its result
- SUM
- Total
- AVERAGE
- Mean
- MIN
- Smallest
- MAX
- Largest
What will A1 show?
A 1 A1 contains
=AVERAGE(2,4,9)Show answer
5. 2 + 4 + 9 = 15, and 15 divided by 3 is 5.
Lesson 3: Counting
What is the difference between COUNT and COUNTA?
- COUNT counts numbers, COUNTA counts any non-empty cell
- COUNTA counts numbers, COUNT counts text
- They are identical
- COUNTA counts empty cells
Show answer
COUNT counts numbers, COUNTA counts any non-empty cell. Remember COUNTA as "count all".
How many products are listed? Count the names in A2:A7 and put the result in A8.
A B C D 1 Product Region Units Price 2 Notebook East 120 3.5 3 Pen West 340 1.25 4 Stapler East 45 12 5 Binder North 80 6.75 6 Marker West 210 2.4 7 Folder North 150 1.9 8 Hint: Product names are text, so COUNT will not work here.
Show answer
=COUNTA(A2:A7)in A8. COUNTA counts text, numbers and anything else that is not empty.What will B1 show?
A B 1 3 2 x 3 7 B1 contains
=COUNT(A1:A4)Show answer
2. COUNT only counts numbers: 3 and 7.
Some prices are missing. Count the blank cells in D2:D7 into D8.
A B C D 1 Product Region Units Price 2 Notebook East 120 3.5 3 Pen West 340 4 Stapler East 45 12 5 Binder North 80 6 Marker West 210 2.4 7 Folder North 150 8 Hint: There is a function made exactly for empty cells.
Show answer
=COUNTBLANK(D2:D7)in D8. COUNTBLANK is handy for checking data before you trust a total.Build the price spread: highest minus lowest