Incrementing a range within a forumula

[Phosphonothioic]

Here's the formula:

=SUMIF('Sheet1!$H$979:$H$1926,$B6,'Sheet1!R$979:R$1926)

I need the R's in the sum_range to increment by a given number of letters when I drag the formula across the sheet.

Let's say 2 letters for simplicity. So R would become T in the next cell.


Is this possible? Thanks!!

 

[AlphaFrog]

Try something like this...

=SUMIF(Sheet1!$H$979:$H$1926,$B6,[COLOR="Red"]OFFSET(Sheet1!$R$979:$R$1926,0,(COLUMN(A1)-1)*[COLOR="Blue"]2[/COLOR])[/COLOR])

The 2 is the number of columns you want to increment.

 

[Michael M]

Hi
Works fine for me by dragging across. I'm in Excel 07
Can't remember, but I thought the CTRL key did that.

 

[Phosphonothioic]

Try something like this...

=SUMIF(Sheet1!$H$979:$H$1926,$B6,[COLOR=red]OFFSET(Sheet1!$R$979:$R$1926,0,(COLUMN(A1)-1)*[COLOR=blue]2[/COLOR])[/COLOR])

The 2 is the number of columns you want to increment.

I'm not having any luck getting

OFFSET(Sheet1!$R$979:$R$1926,0,(COLUMN(A1)-1)*2)

to work with a range of cells. This line gives me #VALUE! when evaluated by itself...

 

[AlphaFrog]

I'm not having any luck getting

OFFSET(Sheet1!$R$979:$R$1926,0,(COLUMN(A1)-1)*2)

to work with a range of cells. This line gives me #VALUE! when evaluated by itself...

It returns a range of cells. If you evaluate it by itself, it will evaluate to #VALUE! just as if you were to evaluate =Sheet1!$R$979:$R$1926 by itself.

I don't know what the problem is. It works in my tests. Did you modify the formula? If yes, can you show the exact formula you used?

The OFFSET function simply offsets by n columns from Sheet1!$R$979:$R$1926

The column incrimination is this part. (COLUMN(A1)-1)*2. If you put this in any cell...
=(COLUMN(A1)-1)*2
...and drag it right, you can see how the column offset increments (0, 2, 4, ...etc)