13.1 The SUM Function

SUM adds a list of numbers. It is the single most-used function in Excel and the starting point for all financial work.

Syntax

• number1 (required): the first value, cell reference, or range to add
• [number2], [number3]… (optional): additional values, references, or ranges. Up to 255 arguments allowed.

SUM Examples

How SUM Handles Different Cell Types

AutoSum Shortcut — The Fastest Way to SUM

• Click the cell below (or to the right of) a data range → press Alt+=
• Excel detects the adjacent range and inserts =SUM() automatically → press Enter to confirm
• To sum multiple columns at once: select the entire blank row below all data columns → press Alt+= → all SUM formulas are inserted simultaneously

SUMIF — Conditional Sum (Preview)

When you only want to sum values that meet a condition (e.g., only Gauteng sales, only amounts above R10,000), use SUMIF or SUMIFS — covered fully in Module 19.

13.2 The AVERAGE Function

AVERAGE calculates the arithmetic mean: it adds all the numbers in a range and divides by the count of those numbers. It is used for performance analysis, grade calculations, sales averages, and any situation where you need a representative midpoint value.

Syntax

AVERAGE Examples

How AVERAGE Handles Different Cell Types

AVERAGEIF & AVERAGEIFS (Conditional Averages)

Weighted Average (Manual Method)

When different values should count more than others (e.g., a final exam worth 60% and coursework worth 40%):

13.3 The COUNT Functions

Excel has a family of COUNT functions, each counting different types of cells. Choosing the right one is essential — using the wrong COUNT function is a very common mistake that gives incorrect results silently.

COUNT — Count Cells with Numbers Only

• Counts only cells that contain numeric values (including dates and times, which are stored as numbers)
• Ignores: text cells, blank cells, error cells
• Use COUNT when: counting how many numeric entries are in a column (e.g., how many sales amounts have been entered, how many students have a score recorded)

COUNTA — Count All Non-Empty Cells

• Counts cells that contain anything — numbers, text, dates, TRUE/FALSE, errors, or any other content
• Ignores: truly empty/blank cells only
• Use COUNTA when: counting the number of records in a list regardless of data type (e.g., how many employee names are in column A, how many rows have any entry at all)

COUNTBLANK — Count Empty Cells

• Counts cells that are completely empty (blank)
• Also counts cells that contain only an empty string (="") — they appear blank but technically contain a formula
• Use COUNTBLANK when: finding missing data — how many employees do not yet have a salary entered, how many students are missing a grade

COUNTIF — Count Cells Meeting a Condition

• range: the cells to check
• criteria: the condition to match — a number, text, cell reference, or comparison expression

COUNTIFS — Count with Multiple Conditions

Comparison: COUNT Family

13.4 The MAX Function

MAX returns the largest numeric value in a range. It is used to find the top performer, the highest invoice amount, the maximum temperature, or the peak sales figure.

Syntax

MAX Examples

How MAX Handles Different Cell Types

• Numbers: considered in finding the maximum
• Blank cells: ignored (not treated as 0)
• Text: ignored
• Logical TRUE: ignored when in a range reference; counted as 1 when entered directly as an argument

Finding the Nth Largest Value — LARGE

When you need the 2nd largest, 3rd largest, etc.:

MAXIFS — Maximum with Conditions (Excel 2019+)

Practical Uses of MAX in SA Offices

• Top salesperson: =MAX(SalesTable[Revenue]) → find the highest revenue; then use INDEX/MATCH to find whose it was
• Latest date: =MAX(A2:A100) on a date column → returns the most recent date
• Highest invoice: =MAX(InvoiceAmount) → find the largest single invoice
• Next ID number: =MAX(A2:A100)+1 → auto-generate the next sequential ID

13.5 The MIN Function

MIN returns the smallest numeric value in a range. It is used for finding the lowest salary, the minimum stock level, the earliest date, or the smallest measurement in a dataset.

Syntax

MIN Examples

Finding the Nth Smallest Value — SMALL

MINIFS — Minimum with Conditions (Excel 2019+)

Practical Uses of MIN in SA Offices

• Reorder alert: =IF(MIN(StockLevel)<10,"REORDER NOW","OK") → alert when any stock falls below 10
• Earliest date: =MIN(A2:A100) on a date column → returns the oldest/earliest date
• Lowest salary: =MIN(EmployeeList[Salary]) → identify the lowest-paid employee for payroll review
• Minimum payment: =MAX(MIN_PAYMENT, MIN(Balance, Full_Amount)) → calculate a minimum instalment payment

13.6 Quick Statistics in the Status Bar

You do not always need to write a formula to see SUM, AVERAGE, COUNT, MAX, and MIN. When cells are selected, Excel shows these values instantly in the Status Bar at the very bottom of the screen — the grey bar that normally shows "Ready".

Reading the Status Bar

1. Select any range of cells containing numbers (e.g., B2:B50)
2. Look at the Status Bar at the bottom of the Excel window
3. By default, you see: Average: [value]    Count: [value]    Sum: [value]

Adding More Indicators to the Status Bar

1. Right-click anywhere on the Status Bar
2. A menu appears with all available statistics — tick to add:

13.7 Combining SUM, AVERAGE, COUNT, MAX & MIN

These functions become much more powerful when combined with each other or with other functions to answer complex business questions.

Practical Combined Formula Examples

13.8 SA Office Scenarios — Practical Application

Payroll Summary Dashboard

Sales Performance Report

13.9 Quick Self-Check

Q1: A column contains: 100, 200, blank, 300, "N/A" (text). What does =SUM(A1:A5) return, and what does =COUNT(A1:A5) return?

✓ =SUM(A1:A5) returns 600. SUM adds the three numeric values (100+200+300=600) and ignores the blank cell and the text "N/A". =COUNT(A1:A5) returns 3. COUNT counts only the numeric cells (100, 200, 300) and ignores both the blank cell and the text "N/A".

Q2: You have 10 employees. Their performance scores are in B2:B11. Three employees have not yet received scores — those cells are blank. One employee scored exactly 0. What does =AVERAGE(B2:B11) calculate, and why might the result be misleading?

✓ =AVERAGE(B2:B11) ignores the three blank cells entirely — it sums the 7 scores that exist (including the 0) and divides by 7. This means it calculates the average for the 7 who have been scored. If you want the average across all 10 employees (treating missing scores as 0), you should not use AVERAGE — instead use =SUM(B2:B11)/10. The result from AVERAGE may be misleadingly high because it excludes the three employees who have no score yet. Always verify whether blank means "no data yet" (exclude) or "scored 0" (include as 0) before choosing your formula.

Q3: You have a list of 200 transactions in column C (C2:C201). Write a formula that counts how many transactions are above R50,000 AND occurred in the Gauteng region (Gauteng is in column B).

✓ =COUNTIFS(B2:B201,"Gauteng",C2:C201,">50000"). COUNTIFS allows multiple conditions: criteria_range1 is B2:B201 with criteria "Gauteng", and criteria_range2 is C2:C201 with criteria ">50000". Only rows where BOTH conditions are met are counted. Note the greater-than symbol and 50000 must be inside quotes when used as a criteria string in COUNTIFS.

Q4: You want to display a message "REORDER NOW" if the minimum stock level in C2:C100 falls below 5, and "Stock OK" otherwise. Write the formula.

✓ =IF(MIN(C2:C100)<5,"REORDER NOW","Stock OK"). MIN(C2:C100) finds the lowest stock level across all 99 products. If that minimum is less than 5, the IF function returns "REORDER NOW"; otherwise it returns "Stock OK". This is a combined formula: MIN used as the condition test inside an IF function.

Q5: What is the difference between COUNT and COUNTA? Give an example of when you would use each.

✓ COUNT counts only cells containing numeric values (numbers, dates, times). COUNTA counts every non-empty cell regardless of content type — numbers, text, dates, errors, TRUE/FALSE. Example for COUNT: =COUNT(B2:B51) on a salary column tells you how many employees have a salary amount entered (numbers only) — useful for payroll completeness checking. Example for COUNTA: =COUNTA(A2:A51) on a name column tells you how many employees are listed (names are text) — gives the total headcount. If you used COUNT on the name column it would return 0 because names are text.

Q6: You need the highest revenue figure from the Gauteng region only. The revenue is in column D and region is in column C. You are using Excel 2019. Write the formula and name the function used.

✓ =MAXIFS(D2:D201,C2:C201,"Gauteng"). The function is MAXIFS, available in Excel 2019 and later. max_range is D2:D201 (where the values are); criteria_range1 is C2:C201 (where the condition is checked); criteria1 is "Gauteng" (the condition to match). MAXIFS returns the largest value in D where the corresponding cell in C equals "Gauteng". If you have Excel 2016 or earlier (no MAXIFS), use the array formula: =MAX(IF(C2:C201="Gauteng",D2:D201)) entered with Ctrl+Shift+Enter.

✓ Module 13 Complete — You Have Learned:

• SUM — syntax; multiple range arguments; summing entire columns; cross-sheet SUM; how SUM handles blanks (ignored), text (ignored), text-stored numbers (ignored — common mistake), errors (propagated); AutoSum (Alt+= ★) for instant totals; SUMIF preview for conditional sums
• AVERAGE — syntax; multi-range AVERAGE; critical blank vs zero distinction (blanks excluded, zeros included); AVERAGEIF for conditional averages including ignoring zeros; weighted average with SUMPRODUCT
• COUNT family — all 5 functions and when to use each: COUNT (numbers only), COUNTA (all non-blank), COUNTBLANK (empty cells), COUNTIF (one condition), COUNTIFS (multiple conditions); comparison table; wildcard * in COUNTIF for partial text matching
• COUNTIF criteria formats — text ("Gauteng"), numbers (">50000"), cell references (">"&A1), wildcards ("*Pty*"), not-blank ("<>"&"")
• MAX — syntax; multi-range; blank/text ignored; LARGE for 2nd/3rd largest; MAXIFS for conditional max; practical SA uses (top performer, latest date, next ID)
• MIN — syntax; multi-range; SMALL for 2nd/3rd smallest; MINIFS for conditional min; practical SA uses (reorder alert, earliest date, lowest salary)
• Status Bar quick stats — selecting cells shows Average/Count/Sum instantly; right-clicking Status Bar to add Numerical Count, Min, Max; pro tip for spot-checking
• Combining functions — 6 combined formula patterns (% of total with absolute SUM, COUNTIF with AVERAGE threshold, MAX-MIN spread, COUNTA/COUNTBLANK completion %, MIN in IF for reorder alerts, AVERAGE of LARGE top 3)
• SA practical application — full payroll summary formula set (total, average, COUNTA headcount, max/min salary, above-average count, department SUMIF/AVERAGEIF, COUNTBLANK missing records); full sales report formula set