# Sumproduct with a dynamic range...is that the only way?

1. ## Sumproduct with a dynamic range...is that the only way?

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?

2. ## Re: Sumproduct with a dynamic range...is that the only way?

I used this and cot the exact same answers as you did?

=SUMIFS(Data!C:C,Data!\$A:\$A,'Sumproduct Example'!\$A6,Data!\$B:\$B,'Sumproduct Example'!\$B\$3)
Note the use of absoluting in the ranges and cells

3. ## Re: Sumproduct with a dynamic range...is that the only way?

Would using the entire column as the range affect calc speed or does it not affect it as much when using a SUMIFS as opposed to using a SUMPRODUCT?

4. ## Re: Sumproduct with a dynamic range...is that the only way?

With SUMIFS referencing entire columns should not be a problem

Some reading on SUMIFS v SUMPRODUCT

http://exceluser.com/blog/483/excels...is-faster.html

5. ## Re: Sumproduct with a dynamic range...is that the only way?

Sweet! Thanks for the info guys.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1