Offset function in conjunction with Sumif

Excel Functions November 17th, 2007

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.

Reader's Comments

Ian Greathead | 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
Handyexceltips | 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.
Philip | February 22nd, 2009 at 11:23 pm
A typically good tutorial from Shikha, but please note that in the last screenshot, an extra row has been added before the row containing “Original format”; the references in the Sumif/Offset formula all need to drop down one row (or delete that extra row…).
Shikha | February 23rd, 2009 at 5:25 pm
Hi Philip,
Am delighted that you enjoyed going through so many of my articles.
Your comments will be extremely useful for me to rectify the errors as well as provide great inputs for future articles (and of course, help visitors of this site ).
Philip | February 24th, 2009 at 7:21 am
Good instructions, nice clear screenshots: what’s not to like? While I’ve often used ROW and COLUMN (see my suggestion in another of your tut’s), your formula above got me looking at ROWS and COLUMNS… hmmm possiblities!
Janet | April 1st, 2009 at 9:01 pm
Could you please help me with a problem after I have my information correct from using vlookup? I now need to change the information into general text so I can upload it into another program. The column still only has the vlookup formula in it.
thanks for any help you can send to me !
Shikha | April 2nd, 2009 at 4:20 pm
Hi Janet

Just select all the cells using CTRL+A and do CTRL+C and then go to edit - paste special-values. This will convert all your formulas to text.
Hope it helps.