Summary functions in google sheet

Arun Kr. Khattri
googlesheet
Page content

Summary Functions in Google Sheet

Quoting OpenOffice.org 3.x Calc Guide “Formulas are equations using numbers and variables to get a result. In a spreadsheet, the variables are cell locations that hold the data needed for the equation to be completed. A function is a predefined calculation entered in a cell to help you analyze or manipulate data in a spreadsheet. All you have to do is add the arguments, and the calculation is automatically made for you.”

Google sheet includes 494 functions in 16 types (categories). Common types are date, financial, logical, lookup, text, statistical and some google specific types like google which contains GOOGLETRANSLATE function to translate text from one language into another.

There are around 135 statistical built-in-functions available in google sheets, and if you think none is for you, you can create one (not the scope of this blog, may be some other time…). In this blog we are going to cover group of functions used in summarizing the data and one must be familiar while using spreadsheets. The group includes SUM, COUNT, AVERAGE, MAX, MIN, & MODE applied on numerical data.

Before going for formulas, few words on entering formulas…

  • all formulas need to be started with one of the following symbols: =, + or -. Anything else will be treated like a text.
  • Each cell has the unique address and referred by its position in the grid (columns & rows). A2 refers to column A, second row in the grid.
  • You can enter formula in two ways, either directly into the cell itself, or at the input line.
  • Depending upon the kind of person you are (in using the computer), while selecting range either you can use mouse or if you are like me which prefers keyboard, range can be written as start:end for e.g to select cell A1 to cell A10, we can write A1:A10 directly into formula.
  • When you start typing function, google sheet try to help you out by suggesting the appropriate name of the function, press ↑ and ↓ to navigate and press TAB to accept. Also it will show you the arguments it takes to provide you the output.

SUM

The title for ‘most used function’ in spreadsheet category undoubtedly goes to SUM function.

Syntax

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

value1 - The first number or range to add together.

value2, ... - [ OPTIONAL ] - Additional numbers or ranges to add to value1.
sum function

Figure 1: SUM function

COUNT

Syntax

COUNT(value1, [value2, ...])

value1 - The first value or range to consider when counting.

value2, ... - [ OPTIONAL ] - Additional values or ranges to consider when counting.

COUNT function returns the number of numeric values in a dataset. It ignores the text values.

count function

Figure 2: COUNT function

COUNTA

To count all values including text, zero-length strings and whitespace, use COUNTA function.

Syntax

COUNTA(value1, [value2, ...])

value1 - The first value or range to consider when counting.

value2, ... - [ OPTIONAL ] - Additional values or ranges to consider when counting.
counta function

Figure 3: COUNTA function

COUNTUNIQUE

Above mentioned count functions doesn’t care about duplicate values, if you want to count unique values only, use COUNTUNIQUE…

  • Syntax

    COUNTUNIQUE(value1, [value2, ...])

value1 - The first value or range to consider for uniqueness.

value2, ... - [ OPTIONAL ] - Additional values or ranges to consider for uniqueness.

COUNTBLANK

returns the number of empty cells in a given range.

  • Syntax

    COUNTBLANK(value1, [value2,...])

value1 - The first value or range in which to count the number of blanks.
value2 - [OPTIONAL ] - Additional values or ranges in which to count the number of blanks.
    other count function

    Figure 4: Other count functions

AVERAGE

The average function returns the numerical average (arithmetic mean) value in a dataset, ignoring text.

Syntax

AVERAGE(value1, [value2, ...])

value1 - The first value or range to consider when calculating the average value.

value2, ... - [ OPTIONAL ] - Additional values or ranges to consider when calculating the average value

To have text values considered as 0 values, use AVERAGEA

average function

Figure 5: AVERAGE function

MEDIAN

The median function finds the median or middle value in a list/range of numbers.

Syntax

MEDIAN(value1, [value2, ...])

value1 - The first value or range to consider when calculating the median value.

value2, ... - [ OPTIONAL ] - Additional values or ranges to consider when calculating the median value.
  • Any text encountered in the value arguments will be ignored.
  • MEDIAN returns the middle value if odd number of value arguments are given. With an even number of values, it gives average of two middle values.
  • It ignores empty cells but not ones containing the numeral 0.
median function

Figure 6: MEDIAN function

MODE

Returns the most commonly occurring value in a dataset.

Syntax

MODE(value1, [value2, ...])

value1 - The first value or range to consider when calculating mode.

value2, ... - [ OPTIONAL ] - Additional values or ranges to consider when calculating mode.
mode function

Figure 7: MODE function

MAX

Returns the maximum value in a numeric dataset.

Syntax

Syntax
MAX(value1, [value2, ...])

value1 - The first value or range to consider when calculating the maximum value.

value2, ... - [ OPTIONAL ] - Additional values or ranges to consider when calculating the maximum value.
max function

Figure 8: MAX Function

MIN

Returns the minimum value in a numeric dataset.

Syntax

MIN(value1, [value2, ...])

value1 - The first value or range to consider when calculating the minimum value.

value2, ... - [ OPTIONAL ] - Additional values or ranges to consider when calculating the minimum value.
max function

Figure 9: MAX Function