Offset function in conjunction with Sumif

Sumif(offset) Functions

Sumif function is useful as long as we want data only from one column as sumif only allows a single condition. However we can get around this problem by using a combination of sumif and offset as is illustrated in the example down below.

Suppose you work at some XYZ organisation and it launched product A and B in year 2001, C in 2002, D in 2003 and E in 2004. They have provided you data in one format but require the results to be presented in some other format as shown in the sheet below.

Now we can use the sumif function to get the results in the required format.

The syntax of the sumif function is:
SUMIF(range,criteria,sum_range) OR
SUMIF([Column to be evaluated],[Criteria],[Column to sum if criteria is met])

The main problem with using a plain sumif function here is you will not be able to drag the formula and you will have to change the name of the column from B to C to D to E as you move along 2001,2002,2003 and 2004 as shown below.

The offset function can resolve this issue as it allows multiple conditions and will pick the numbers from the correct column.

The syntax of the offset function is:
Offset( range, rows, columns, height, width)

Although the result is same using both options but using sumif along with offset would spare us from changing the formula as we move down.