How to increment range formulae by 1?

[kkaass]

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!

 

[Rick Rothstein]

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]

Thank you very much for your reply.

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.

 

[Rick Rothstein]

Thank you very much for your reply.

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]

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