Google Sheets OFFSET Function | Embed OFFSET inside SUM | Google Spreadsheet

Published: 02 February 2020
on channel: Software Spring

3,830

Use Google Sheets OFFSET and SUM functions together to extract specific data and sum that data. You have to embed the OFFSET function inside SUM. Google Sheets will first evaluate OFFSET and returns the data to the SUM function, which will then calculate the total.

For example, say there is a cutlery database. The database gives the inventory pertaining to plates, cups, and bowls made from material like steel, ceramic, copper, glass, or plastic. You can use OFFSET and SUM Google Sheets functions to get inventory of cutlery made from a specific material and also the total.

Here is the format of the Google Sheets OFFSET function:

=OFFSET(cell_reference, offset_rows, offset_columns, [height], [width])

Example

=OFFSET(A1, 1, 1, 1, 4)

A1 is the cell_reference or the starting point to offset.

offset_rows=1. It is equivalent to saying, offset or move down by 1 row from A1 or cell reference.

offset_columns=1. It is equivalent to saying, offset or move one column right after moving down 1 row.

height=1. It is optional. It is equivalent to saying that extract data from 1 row.

width=1. It is optional. It is equivalent to saying that extract data from 4 columns.

Here is an example output:

Steel 155 459 782

Here is the format of the Google Sheets SUM function:

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

value1 can be a range or a number.

value2, value3... is optional.

Example:

=SUM(A1:A5)

If the numbers in the range A1 to A5 are 5, 10, 15, 20, 25, the SUM function will return

75

Now, let's embed the OFFSET inside Google Sheets SUM function. Here is the format:

=SUM(OFFSET(A1, 1, 1, 1, 4))

The ouput of the SUM-OFFSET combo, using the values returned by the OFFSET function, is:

1396 (155+459+782)

The SUM function ignores any text in its input range. In this example, it ignores the text, Steel.

This tutorial on Google Sheets OFFSET and SUM functions gives the steps to combine the two functions to extract specific data and sum the same.

On this page of the site you can watch the video online Google Sheets OFFSET Function | Embed OFFSET inside SUM | Google Spreadsheet with a duration of hours minute second in good quality, which was uploaded by the user Software Spring 02 February 2020, share the link with friends and acquaintances, this video has already been watched 3,830 times on youtube and it was liked by like viewers. Enjoy your viewing!

13,912

323