Google Sheets OFFSET Function | Embed OFFSET inside SUM | Google Spreadsheet
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.
Auf dieser Seite können Sie das Online-Video Google Sheets OFFSET Function | Embed OFFSET inside SUM | Google Spreadsheet mit der Dauer stunde minuten sekunde in guter Qualität ansehen, das der Benutzer Software Spring 02 Februar 2020 hochgeladen hat, den Link mit Freunden und Bekannten teilen, dieses Video wurde auf Youtube bereits 3,830 Mal angesehen und es wurde von like den Zuschauern gefallen. Viel Spaß beim Betrachtenden Zuschauern gefallen!
