Recommended resources
The freely available resources this lesson will direct you to.
- Autosum, Average, Max, Min, Count & Autofill Functions | Excelwww.youtube.com
- 6-7. Formulas and functions: Commons functions including SUM, MAX, MIN, COUNT, and AVERAGEwww.youtube.com
- Excel – Basic functions or formulae (SUM, AVERAGE, COUNT) – UOC – Helphelp.chi.ac.uk
- AutoSum in Excel: Calculate SUM, AVERAGE, MAX, MIN, and COUNT - GeeksforGeekswww.geeksforgeeks.org
Welcome back. In the previous lesson, you calculated a variance for each row and copied formulas without losing the intended cell references. Now you’ll answer questions about the whole set of records: What is the total actual amount? What is the average per record? What are the smallest and largest amounts, and how many numeric amounts were recorded?
You’ll use five Excel functions—SUM, AVERAGE, MIN, MAX, and COUNT—in the finance workbook you’ve already built. Allow about 35–40 minutes, including time to enter and check the formulas.
From individual rows to a summary
Your variance_inr formula answers a row-level question: how does this department-month’s actual amount compare with its budget? A summary answers a different question about a range of rows.
In the example workbook, actual_inr occupies D2:D5 and contains 110, 95, 75, and 88. budget_inr occupies E2:E5 and contains 100, 105, 80, and 80. The colon in D2:D5 means “every cell from D2 through D5.” Each of the five functions below can take such a range as its input.
Watch Kevin Stratvert’s 6-7. Formulas and functions: Common functions including SUM, MAX, MIN, COUNT, and AVERAGE. His examples use sales rather than finance amounts, but the question is the same: which summary answers what the business needs to know?
6-7. Formulas and functions: Commons functions including SUM, MAX, MIN, COUNT, and AVERAGE
Kevin Stratvert demonstrates how a function turns a selected range into a reusable summary, then compares the five functions you’ll use here.
Watch SUM and ranges, paying particular attention to how the colon specifies all cells between two addresses. Then skip to the other functions. Notice that Excel may suggest the wrong range: the function is only useful if you confirm which cells it includes.
A function formula starts with =, followed by the function name and its input in parentheses. For example, =SUM(D2:D5) adds the numbers in those four cells. Unlike a total you might briefly see on Excel’s status bar, a formula leaves a labelled, recalculating result in the worksheet.
For a compact reference, read the University of Chichester’s Excel – Basic functions or formulae (SUM, AVERAGE, COUNT). It also covers MIN and MAX.
Excel – Basic functions or formulae (SUM, AVERAGE, COUNT) – UOC – Help
Use this short University of Chichester guide to confirm what each function returns and how its range is written.
Under “SUM — Add Values,” start at the purpose statement “Adds numbers in a range.” Read through the examples under “AVERAGE — Mean of Values” and “MAX and MIN — Largest and Smallest Values,” then finish with the tip under “COUNT — Count Numeric Cells”: the five functions. Stop before “COUNTA — Count Non-Empty Cells.” Focus on the distinction between adding values and counting cells that contain numbers.
Build a five-line finance summary
Keep the original observations in rows 2–5. In an unused area of the same sheet, put Measure in L1, actual_inr in M1, and budget_inr in N1. Enter Total, Average, Minimum, Maximum, and Numeric count in L2:L6, respectively.
Now enter these formulas in column M:
| Result cell | Formula | Expected result for the example |
|---|---|---|
| M2, Total | =SUM(D2:D5) |
368 |
| M3, Average | =AVERAGE(D2:D5) |
92 |
| M4, Minimum | =MIN(D2:D5) |
75 |
| M5, Maximum | =MAX(D2:D5) |
110 |
| M6, Numeric count | =COUNT(D2:D5) |
4 |
Type each formula into its result cell and press Enter. You can also select a result cell and use the AutoSum dropdown on Excel’s Home or Formulas tab to choose Sum, Average, Min, Max, or Count Numbers. If you use that menu, check the suggested range before accepting it; nearby summary cells can lead Excel to select something other than D2:D5.
The total, 368, is the combined actual amount across the four records. The average, 92, is the arithmetic mean per numeric record: here, the total divided by four. It is not automatically a monthly average or a departmental average—each row is a department-month observation. MIN and MAX return the smallest and largest amounts, not the names of the departments that produced them.
If your imported workbook has different values or rows, use its actual data range. The expected results in the table apply only to the four example amounts above.
Compare like with like—and check the count
The budget amounts are immediately beside the actual amounts. Copy M2:M6 one column right into N2:N6. Because the range references are relative, =SUM(D2:D5) in M2 becomes =SUM(E2:E5) in N2; the other four formulas change in the same way. This is the copying behaviour you worked with last lesson, now applied to summary functions.
For the example data, the budget column should show a total of 365, average of 91.25, minimum of 80, maximum of 105, and numeric count of 4. You can reconcile one result with your existing variance column: actual total 368 exceeds budget total 365 by 3, which should also equal =SUM(F2:F5) if F contains the four row-level variances.
The two counts deserve a deliberate look. COUNT(D2:D5) does not count every occupied row; it counts cells Excel recognizes as numbers. In a range containing 100, a blank cell, 0, and the text pending, COUNT returns 2. Zero is a recorded numeric value and is counted; a blank is not. SUM and AVERAGE also leave the blank and text out of their calculations, while including zero. For those four illustrative cells, the sum is 100 and the average is 50.
Thus, a numeric count of four supports your expectation that all four example actual amounts are stored as numbers. A count below four is a signal to inspect the cells—not proof of what went wrong. An amount might be missing or might look numeric while being stored as text. This distinction will matter when you examine data-quality problems later in the module.
Takeaway
Use SUM for a combined amount, AVERAGE for the mean of numeric values, MIN and MAX for the extremes, and COUNT to establish how many numeric cells contributed. Always specify and verify the range, and interpret the result in terms of what one row represents. In the example, the four actual amounts total 368, average 92, range from 75 to 110, and have a numeric count of 4.
Next, you’ll sort and filter records to inspect the particular rows behind a business question, rather than summarizing every row at once.