SUMIF with sum_range data offset by one row down

[nocturnal028]

All,

This seems like it should be trivial but I am stumped and could use a quick nudge in the right direction.

I have a spreadsheet with a bunch of imported records in it and I am stuck with the format as it is. I have all of my sumif "range" data located in column C. All of the sumif "sum_range" data is located in column I. The problem is, the data I am trying to sum in column I is offset down one row from my matching condition in column C. So, for the first match, the match happens in C12 but the sum number is in I13; for the second match, the match is in C18 and the sum number is in I19, Etc. This offset is always the same (row+1) through potentially hundreds of records.

If the data lined up, I would have an easy formula of =SUMIF(C:C, criteria, I:I) but since the I column data is offset by one row down, I need something different. It doesn't appear that I can simply offset the I:I address.

Is there an easy way to make the sumif look one row lower for the sum number? Or am I barking up the wrong tree? I searched the board for possible solutions and found a few things kind of similar but not exact so I apologize if I missed a posting to solve this.

All suggestions are greatly appreciated!

 

[njimack]

Rather than summing the entire column, make use of dynamic ranges. You can then create the sum range to offset the criteria range by 1 row.

Excel Names -- Excel Named Ranges

 

[Andrew Poulsom]

You can't offset an entire column but you can use eg:

=SUMIF(C1:C4999,criteria,I2:I5000)

 

[nocturnal028]

Thanks Andrew! I knew there was an obvious (to somebody) answer out there. That works perfectly for my application!