Essential Functions

By the Syncfusion DevTutorials team · Last updated

Use SUM, AVERAGE, MIN, MAX, and COUNT, enter functions three ways, and give them the right arguments.

Adding dozens of cells using + operators can be slow and difficult to manage. Functions are built-in formulas that perform common calculations with a short and simple instruction. In this lesson, you'll learn how to use Excel's most frequently used functions: SUM, AVERAGE, MIN, MAX, and COUNT. You'll also learn how functions are structured and how to provide arguments.

Open the Excel sandbox and follow along.

What a Function Looks Like

Structure of a Function

=SUM(B2:B10)
 │   │
 │   └── Argument: the data the function uses
 └────── Function name

Every function contains:

  • A function name
  • Parentheses ()
  • One or more arguments inside the parentheses

Arguments tell Excel which values or cells to use in the calculation.

SUM()

SUM adds numbers together.

Examples: =SUM(B2:B10)

Adds all values from B2 through B10.

=SUM(B2:B10,D2:D10)

Adds two separate ranges.

=SUM(B2,B5,B9)

Adds individual cells.

Text values and blank cells are ignored.

The AutoSum (Σ) button automatically creates a SUM formula for you. Select the cell below a column of numbers, click AutoSum, verify the suggested range, and press Enter.

AVERAGE()

AVERAGE calculates the mean value of a group of numbers.

Example: =AVERAGE(B2:B10)

Excel adds the numbers and divides the total by the number of numeric values.

Blank cells are ignored, but cells containing 0 are included in the calculation.

MIN()

MIN returns the smallest number in a range.

Example: =MIN(B2:B10)

Common uses include:

  • Lowest score
  • Lowest price
  • Coldest temperature
  • Smallest quantity

MAX()

MAX returns the largest number in a range.

Example: =MAX(B2:B10)

Common uses include:

  • Highest score
  • Largest sale
  • Highest temperature
  • Biggest expense

SUM, AVERAGE, MIN, and MAX ignore text values and empty cells. If a result seems incorrect, check whether some numbers are stored as text instead of numeric values.

COUNT()

COUNT returns the number of cells that contain numeric values.

Example: =COUNT(B2:B10)

The COUNT family includes several related functions:

FunctionCounts
COUNTCells containing numbers or dates
COUNTANon-empty cells, including numbers and text
COUNTBLANKEmpty cells

For example, if you're counting names in a list, use COUNTA instead of COUNT because names are text values.

Entering Functions

You can enter functions in several ways.

Type the Function

  1. Type =.
  2. Begin entering the function name.

For example: =AV

Excel displays matching functions.

  1. Select the function.
  2. Press Tab.

Use AutoSum

The AutoSum (Σ) button provides quick access to:

  • SUM
  • AVERAGE
  • COUNT Numbers
  • MAX
  • MIN

Use Insert Function

  1. Click the fx button beside the Formula Bar.
  2. Search for a function.
  3. Select it.
  4. Enter the required arguments.

Function Arguments

Arguments appear inside the parentheses and are separated by commas.

Examples of arguments include:

A Range

=SUM(B2:B10)

Individual Cells

=SUM(B2,B5,B8)

Numeric Values

=MAX(B2:B10,100)

This formula returns either the highest value in the range or 100, whichever is larger.

You can also select ranges with the mouse while entering a function.

For example:

  1. Type: =SUM(
  2. Select cells B2 through B10.
  3. Type: )
  4. Press Enter.

Function ranges should include only the actual data. Do not include column headings, and never include the cell containing the function itself. Including the formula cell creates a circular reference.

Common mistakes

MistakeResultSolution
Excluding the last data row from a rangeThe result is too smallExtend the range to include all data
Including the total cell within its own formulaCircular reference warningExclude the total cell from the range
Using COUNT on text valuesReturns 0Use COUNTA instead
Numbers stored as textFunctions ignore themConvert them to real numbers
Missing a closing parenthesisFormula errorCheck that every opening parenthesis has a matching closing parenthesis

Exercise

Click Reset in the sandbox header and enter the following data:

  • A1: Student, B1: Score
  • A2: Ana, B2: 72
  • A3: Ben, B3: 85
  • A4: Cara, B4: 64
  • A5: Dev, B5: 90
  • A6: Eli, B6: 79

Then complete each task and click Check.

What to Remember

  • Functions use the format =FUNCTION(arguments).
  • Arguments are placed inside parentheses and separated by commas.
  • SUM adds values.
  • AVERAGE calculates the mean.
  • MIN finds the smallest value.
  • MAX finds the largest value.
  • COUNT counts numeric values.
  • COUNTA counts all non-empty cells.
  • COUNTBLANK counts empty cells.
  • AutoSum (Σ) quickly creates common functions.
  • Include only data in a function range, never headings or the formula's own cell.

In the next lesson, you'll make Excel perform logical decisions using IF, AND, OR, NOT, COUNTIF, and SUMIF.