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.
Share ThisPopularity: 23%
September 18th, 2008 at 9:09 am
The problem I have is being able to sum (in the above examples case) two or three years at once. You should be able to to do this by adding 2 or 3 to the width option in the offset, but this doesn’t work. Any ideas?
Any help is gratefully received.
Cheers,
Ian
September 26th, 2008 at 7:34 am
Sumif would not work in this case as it can only take one column at a time. Even if we define the width as 2, it will still take the first column. In order to sum more than one column, we can either use sum function in combination with offset or array functions.
Hope this helps.