How to Have a Formula Automatically Update When Adding

[EverClever]

Hello All,

The basic formula configuration is

=(F5*I5)+(F6*I6)......+(F59*I59)

This formula calculates correctly but every time I expand the formula to include a new row, I have to manually update this formula. Is there a function or another formula configuration that would accomplish this?

Thank you,

Patrick

[Stormin']

=SUMPRODUCT(F5:F59,I5:I59)
is the shorter version of your formula.

Knowing that you can then drag down the range every time a new row is added, is there still a significant efficiency gain from automating the process?

[SlipEternal]

Try

Please Login or Register  to view this content.

[AliGW]

Try this:

=SUMPRODUCT(F5:F1000,I5:I1000)

Make the arrays as long as you need.

[ChemistB]

Assuming that the rows without data are blank or null value,

=SUMPRODUCT(F5:F1000, I5:I1000)
choosing a cell with enough safety that you will not exceed.
Alternately, you could name the two ranges as dynamic ranges and they will expand with the data, using the same formula.

Wow, I'm slow. Good work guys! LOL

[sweep]

Hi,

To automatically adjust,

=SUMPRODUCT(F5:INDEX(F5:F10000,MATCH(9.999E+307,F5:F10000,1)),I5:INDEX(I5:I10000,MATCH(9.999E+307,I5:I10000,1)))

[EverClever]

Stormin', SlipEternal, AliGW,

Thanks much for your input. However, some of the cells are blank and it results in a #N/A error message. How is that resolved?

Patrick

[SlipEternal]

=SUMPRODUCT(IFERROR(F5:F10000,0),IFERROR(I5:I10000,0))

After typing in the formula (but still editing it), press CTRL-SHIFT-ENTER so that Excel treats it as an array formula.

[ChemistB]

Blank cells should not result in errors for the basic equations shown above. Can you upload an example spreadsheet? (Go Advanced>Manage Attachments)

[EverClever]

SlipEternal,

Your formula worked! Thanks so much!

Patrick