CODEWITHSUNDEEP
excelsumexcel-indirect
dpowers
dpowers

Reputation: 11

Dynamic SUMPRODUCT to Auto Update with New Row

I am trying to figure out a way to have a dynamic SUMPRODUCT that automatically update when a new row (expense) is inserted with a different unit price. I'm currently using =SUM(INDIRECT("D23 :"&ADDRESS(ROW()-2,COLUMN())) for the sum version to get the total number of units, but I need to do a SUMPRODUCT in order to total different cost per units with the number of units resulting in the total cost for that particular service. The columns are the different services being provided. The SUMPRODUCT isn't working with the INDIRECT and ADDRESS nested to make the arrays. Due to needing more than one total at the bottom for error checking, I cannot use a table. Any help would be greatly appreciated!

Upvotes: 0

Views: 1730

Answers (1)

teylyn
teylyn

Reputation: 35935

You can use a mix of absolute and relative referencing instead of Indirect. Consider this screenshot:

enter image description here

The range is referred to with $F$7:F12 If more row are inserted above the total row, the formula in the total will change to =SUM($F$7:F13), i.e. the range always starts in F7 and will extend to one cell above the current cell.

The formula in F14 calculates the average and uses =AVERAGE($F$7:F12). When another row is inserted above the total row, the formula will change to =AVERAGE($F$7:F13), i.e. start at cell F7 and extend the range to two cells above the current cell.

This concept can be used without issues in all formulas that use ranges.

Upvotes: 0

Related Questions