Level 1 · Beginner: data, formatting & basic formulas

1.3Essential functions: SUM, AVERAGE, MIN, MAX, COUNT

Goal: aggregate a range with SUM, AVERAGE, MIN, MAX and count with COUNT/COUNTA.

Estimated time: 13 min · self-paced reading · If you only have 5 min: read “The essentials in 30s” + the diagram.

The essentials in 30 seconds

Name + range, not cell by cell: =SUM(B2:B13) adds, =AVERAGE(...) averages, =MIN(...) / =MAX(...) bound — you pass a range, never =B2+B3+….
Counting right: COUNT counts numbers, COUNTA counts non-empty cells (numbers + text), COUNTBLANK the empty ones — confusing COUNT and COUNTA skews everything «per item».
The Σ button thinks for you: AutoSum (Σ) proposes the adjacent range, typing =AVER suggests the function — faster to type, zero typos.

Running mini-case — Wassim closes the month

End of month: Wassim must total sales B2:B13, give the average, the smallest and the largest sale, then count how many days actually sold. He writes =SUM(B2:B13), =AVERAGE(B2:B13), =MIN(B2:B13), =MAX(B2:B13) and =COUNT(B2:B13) — an «N/A» cell (a day with no entry) is ignored by AVERAGE but counted by COUNTA, hence his choice of COUNT. Closing wrapped up in five formulas, without a calculator.

Continuation of case 1.1-1.2 — Wassim moves from single formulas to the month's aggregations.

1. A function = a name + arguments

A function is written =NAME(arguments). The most useful ones aggregate a range: =SUM(B2:B13) adds, =AVERAGE(...) averages, =MIN(...)/=MAX(...) give the smallest/largest. You pass a range (B2:B13) rather than adding cell by cell.

In English, the names are: SUM, AVERAGE, MIN, MAX, COUNT, COUNTA — with the comma separator between arguments when there are several.

2. Counting: COUNT vs COUNTA

COUNT counts the numbers in a range; COUNTA counts the non-empty cells (numbers AND text). To count empty cells, COUNTBLANK. Confusing COUNT and COUNTA skews averages and totals «per item».

Wassim's case: an «N/A» in B2:B13 — COUNT says 11 days sold, COUNTA says 12 filled cells. For «how many valid sales», it's COUNT.

3. Auto-complete and Σ

The AutoSum (Σ) button proposes the adjacent range in one click. By typing =AVER, Excel suggests the functions: you pick, you complete the arguments. It's faster and avoids typos in function names.

Wassim types =SUM then Tab: the function is inserted, he only has to aim at the range. Five functions without a single mistake.

Common misinterpretation: « =B2+B3+B4 is enough, and COUNT counts everything that is filled ». No: adding by hand breaks at the first added row (the SUM range, for its part, extends), and COUNT only counts numbers — an «N/A» makes it lie if you expected COUNTA. Range + the right counting function, always.

Add, average, find the extremes, count: the foundation of calculations on ranges.

Text version of the diagram (SEO / screen readers / translation): 1) Aggregate: Wassim aims at the month's range B2:B13 with =SUM(B2:B13) for the total and =AVERAGE(B2:B13) for the average — the text «N/A» is ignored. 2) Bound: =MIN(B2:B13) and =MAX(B2:B13) give the range of sales without sorting. 3) Count: =COUNT(B2:B13) counts the days sold (numbers), =COUNTA(B2:B13) would also count the «N/A». Lesson: one range, five functions, and COUNT when you want numbers.

🔎 Concrete example — 🔎 Concrete example — Analyzing a sales column

Sales in B2:B13 (including a text cell «N/A»):

=SUM(B2:B13)     →  total of the numbers
=AVERAGE(B2:B13) →  average of the numbers (ignores text)
=MAX(B2:B13)     →  best sale
=COUNT(B2:B13)   →  11  (numbers)
=COUNTA(B2:B13)  →  12  (also counts «N/A»)

Reading: SUM and AVERAGE ignore text; COUNT and COUNTA, for their part, do not count the same way.

Lesson: for «how many valid sales?» use COUNT, not COUNTA.

Section vocabulary

Function

Predefined calculation =NAME(arguments), in English: SUM, AVERAGE, MIN, MAX, COUNT.

Simple example: =SUM(B2:B13) totals Wassim's month.

What it is not: a manual formula =B2+B3+B4+… cell by cell.

Reflex: from three cells, switch to the range function.

Range

Rectangular block written B2:B13, passed as a single argument to functions.

Simple example: B2:B13 covers the twelve selling days.

What it is not: a list of scattered cells typed one by one.

Reflex: aim at the range, include neither the header nor the total.

COUNT / COUNTA

COUNT counts numbers; COUNTA counts non-empty cells (numbers + text).

Simple example: COUNT = 11 days sold, COUNTA = 12 with the «N/A».

What it is not: two interchangeable synonyms.

Reflex: «valid?» = COUNT; «filled?» = COUNTA.

AutoSum (Σ)

Button that inserts SUM (or AVERAGE, COUNT…) guessing the adjacent range.

Simple example: Wassim clicks Σ under B13: =SUM(B2:B12) proposed.

What it is not: mental math copied by hand.

Reflex: Σ first, then check the proposed range.

In practice — In practice — Aggregate and count

On a column of numbers, compute SUM, AVERAGE, MIN and MAX.
Add a text cell in the range and observe the effect on AVERAGE.
Compare =COUNT(range) and =COUNTA(range).
Use the Σ button to insert a sum in one click.

You aggregate a range and count correctly according to the need (numbers vs non-empty).

Ready-to-say script — presenting a closing (30 s)

« Month total: =SUM(B2:B13). Average: =AVERAGE(B2:B13) — it ignores the «N/A», so not skewed. Range: =MIN and =MAX. And days sold: =COUNT, eleven, not twelve — COUNTA would also count the «N/A». »

Say it while pointing at each result and the «N/A» that makes the COUNT/COUNTA difference.

Copy-paste template — monthly closing

=SUM(B2:B13)     Month total
=AVERAGE(B2:B13) Average (ignores text and blanks)
=MIN(B2:B13)     Smallest sale
=MAX(B2:B13)     Largest sale
=COUNT(B2:B13)   Days sold (numbers only)

Wassim's closing

As above: five formulas on B2:B13 — total, average, bounds, days sold with COUNT.

Weekly cash

=SUM(B2:B8) each week then =SUM(C2:C5) of the four weeks: the month total recalculates by itself.

Quality control

=MIN(B2:B13) under a threshold = alert: the smallest sale signals an abnormal day without scanning the column.

Simple inventory

=COUNTA(B2:B50) for «filled boxes» (text allowed) vs =COUNT(B2:B50) for «entered quantities» — two questions, two functions.

✓ Best practices

Pass a range (B2:B13), not cells one by one.
Choose COUNT or COUNTA according to what you count.
Use Σ and auto-complete.

✗ Common pitfalls

Adding by hand (=B2+B3+…) instead of SUM.
Confusing COUNT (numbers) and COUNTA (non-empty).
Including the header or the total in the aggregated range.

Key takeaways

=NAME(arguments); aggregate a range (B2:B13).
SUM, AVERAGE, MIN, MAX: the foundation.
COUNT counts numbers; COUNTA counts non-empty cells.
The Σ button inserts a sum in one click.

48h challenge — apply tomorrow

On a real column, write =SUM, =AVERAGE, =MIN, =MAX, compare =COUNT and =COUNTA and hunt down the text cell that explains the gap.

Section exit checklist

I aggregate a range with SUM, AVERAGE, MIN, MAX.
I count with COUNT (numbers) or COUNTA (non-empty).
I insert quickly with Σ and auto-complete.

Self-assessment 1-5: do I pass ranges rather than manual additions?do I choose COUNT vs COUNTA according to the question asked?

← Previous : First formulas: + − × ÷ & order of operations (V3)Next : Relative, absolute ($) & mixed references (V3) →

Frequently asked questions

Does AVERAGE count empty cells?

No: AVERAGE ignores empty cells and text, and divides by the number of numeric values. That is why a range with gaps does not skew the average — but be careful if some zeros should count.

How do I count the cells that meet a condition?

With COUNTIF (seen in level 3), e.g. =COUNTIF(B2:B13,">100"). COUNT/COUNTA count without a condition; COUNTIF adds a criterion.

Check your understanding

Which function counts only the cells containing numbers?

Additional resources (optional, to go further)

📘

SUM, AVERAGE… (Microsoft support)

support.microsoft.comEN

Videos « SUM AVERAGE functions Excel »

YouTubeEN

Level 1 examFresh, harder questions to test what you've learned