# How to increment range formulae by 1?

#### kkaass

##### Board Regular
Hi,

I have a formula in one of my cells sumproduct(index(F2:G31,0,1),H2:H31)

So for instance up till now, the above formula considers all values from F2:F31, G2:G31 and H2:H31. The rows beneath these cells are empty for now.

How should I set the above formula such that if I enter a value in F32, G32 and H32, the above formula should change as:
sumproduct(index(F2:G32,0,1),H2:H32)

Then if I enter values in F33, G33 and H33, the above formula should run for:

sumproduct(index(F2:G33,0,1),H2:H33)

So whenever I add values in the following cells of column F,G and H, the above formula takes into account all the cells up till those cells.

I think I might need to use 'range' somehow but I am not sure about the exact formula.

I will be very grateful if I can get some help with this!

### Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

#### Rick Rothstein

##### MrExcel MVP
First off, while you show a range F:G, you are ignoring the values in Column G in your calculation. Assuming that is correct, and further assuming cell F1 is a header cell that is not empty), give this formula a try...

=SUMPRODUCT(INDEX(F:F,2):INDEX(F:F,COUNTA(F:F)),INDEX(H:H,2):INDEX(H:H,COUNTA(F:F)))

#### kkaass

##### Board Regular

Yes I am aware that the above mentioned formula ignores the values in column G, but thats how its supposed to be. Sorry, I didn't mention the whole model.
Basically, I did not mention the other formula which ignores values in column F.
So there are two formulae:

sumproduct(index(F2:G31,0,1),H2:H31) (ignoring values in column G)
sumproduct(index(F2:G31,0,2),H2:H31) (ignoring the values in column F)

Its an optimization model, so the constraints are defined as above.

Considering the above scenario, should I still use the formula you provided, or is there a way where I can keep the above form (that is include column G in both formulae) but have a range?

Yes, the values are starting from F2, G2 and H2. F1, G1 and H1 are headers as you said, name given to that column.

Last edited:

#### Rick Rothstein

##### MrExcel MVP

Yes I am aware that the above mentioned formula ignores the values in column G, but thats how its supposed to be. Sorry, I didn't mention the whole model.
Basically, I did not mention the other formula which ignores values in column F.
So there are two formulae:

sumproduct(index(F2:G31,0,1),H2:H31) (ignoring values in column G)
sumproduct(index(F2:G31,0,2),H2:H31) (ignoring the values in column F)

Its an optimization model, so the constraints are defined as above.

Considering the above scenario, should I still use the formula you provided, or is there a way where I can keep the above form (that is include column G in both formulae) but have a range?

Yes, the values are starting from F2, G2 and H2. F1, G1 and H1 are headers as you said, name given to that column.
Well, if you lock the Column H references, then you can copy the formula across to the next column and it will automatically update the Column F reference to Column G like so...

=SUMPRODUCT(INDEX(F:F,2):INDEX(F:F,COUNTA(F:F)),INDEX(\$H:\$H,2):INDEX(\$H:\$H,COUNTA(F:F)))

However, if you plan to put the formula for Column G in a cell other than in the next column, then you can use this instead...

=SUMPRODUCT(INDEX(G:G,2):INDEX(G:G,COUNTA(G:G)),INDEX(H:H,2):INDEX(H:H,COUNTA(G:G)))

#### kkaass

##### Board Regular
Thank you very much for your help! I will try these formulae both for F and G columns.

Replies
5
Views
322
Replies
5
Views
408
Replies
11
Views
2K
Replies
0
Views
244
Replies
12
Views
3K

1,195,651
Messages
6,010,930
Members
441,573
Latest member
Goronvir

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back