Are using dynamic ranges the only way to make the rows in a SUMPRODUCT formula not change if rows are deleted/added on the data set? The reason why I ask is if I have a set of data where there is a column for each month, that would mean that I would need to create 12 dynamic ranges for each month. If I have say 2 or 3 data sets then I would need to create even more dynamic ranges. Is there an easier way that you guys use or are dynamic ranges the only option? I have attached an example. In the example I have set the SUMPRODUCT formula to go down to row 50. If I add or delete rows on the data set, the formula will change accordingly. Is there a way to keep the row anchored at row 50 without using dynamic ranges? The only thing I can think of would be to use the INDIRECT function inside of the SUMPRODUCT function. Are there any other alternatives?
Bookmarks