Offset allows you to reference another location from a fixed point using variables to define the new location and size of the new location.
eg:
=Offset(a1,5,2)
will return the value from C6, A+2=C, 1+5=6
=Sum(Offset(A1,5,2,7,3))
will sum the area from C6:E12
That is an area 7 Rows High, 5 Columns wide
with Top Left corner at D6
The advantage is that we can use cells references instead of fixed num,bers to define the location, size and extent of the modified range.
This leads to connection to controls for moving reports/charts and the use in Dynamic Ranges or Ranges that expand as data is added to them.
eg: Put this somewhere
=Sum(offset(A1,0,0,counta(a:a),1)
or
=Sum(offset(A1,,,counta(a:a),1)
now start entering data in Column A
You now have a range that is expanding as you add more data to the column.