Functions are the lifeblood of spreadsheets.

They're amazing little tools that take data in, calculate or manipulate it, and return the new data you need.

A function in a formula bar looks like this:

=FUNCTION(data1,data2)

You'll get to know the name of many functions from the hundreds available to you.

Some of the more common functions include SUM, AVERAGE, COUNT, MIN, MAX.

The data points you feed these functions (e.g. data1,data2) are called 'arguments'.

Functions can require a certain number of arguments (including none at all), others can take as many arguments as needed, and some have optional arguments that are only required for the function to work in a specific way.

SUM

Let's use the SUM function as an example.

SUM returns the sum of a series of numbers and/or cells.

SUM can take numbers, cell references, cell ranges, or a combination of all three.

Sample Usage

AB
1Numbers=SUM(1,2,3,4,5)
2Cell references=SUM(A1,A2)
3Cell ranges=SUM(A2:A100)
4Combination=SUM(1,A2,A4:A50)

Syntax

=SUM(value1, [value2, ...])

The square brackets around [value2, ...] mean it's optional.

The ellipsis (...) at the end means that SUM can take as many arguments as you need. The only required argument is the first one.

Functions within functions

The beauty of spreadsheet functions is that they can be ‘nested’.

This means an argument for one function can be the output of another.

Let’s use the following sales data as an example:

ABCD
1SalespersonJanFebMar
2Jim Halpert$5,000$7,000$5,500
3Dwight Schrute$6,000$6,500$5,900
4Andy Bernard$1,000$3,000$2,500
5Stanley Hudson$4,000$5,500$5,750
6Phyllis Vance$3,000$6,000$6,000

You want to get the total sales for the three months.

You could do this using any of the following (with the last being the easiest to understand and therefore the best):

You could also do the following:

This shows what nesting looks like.

Each argument to the outer SUM function is itself a sum function. You’re summing the sum of each year’s sales figures.

The internal SUM functions SUM(B2:B6),SUM(C2:C6),SUM(D2:D6) are executed first with the values they return then used are arguments to the outer SUM function: =SUM(19000, 28000, 25650).

It’s not the best way to use the sum function, but it shows that the arguments of one function can be the output of another.

Function Exercises
Enter a formula for each question in the formula bar for the spreadsheet below to access the next lesson.
1. Numbers
In cell B8, enter a formula that uses a function to calculate the sum of Dwight and Jim's sales using numbers (not references). Remember to start your formula with '='.
2. Cells
In cell B9, enter a formula that uses a function to calculate the sum of Dwight and Jim's sales using cell references (not ranges).
3. Ranges
In cell B10, enter a formula that uses a function to calculate the sum of everyone's sales using a range reference (not cells).
ABC
1FirstLastSales
2DwightSchrute10,000
3JimHalpert8,000
4StanleyHudson7,000
5PhyllisVance6,000
6AndyBernard4,000
7
8Numbers:
9Cells:
10Range:
11
Next Lesson

FREE RESOURCE

Google Sheets Cheat Sheet

Learn 12 tips and tactics that will make your sheets more beautiful and user-friendly from today.

Google Sheets Cheat Sheet

By entering your email address you agree to get email updates from me. I'll respect your privacy and you can unsubscribe at any time.