Dynamic SUMIFS range (Dynamic OFFSET)

Hi,

I need some help with a SUMIFS formula that will have a dynamic range whenever I dump the data into the spreadsheet. I've attached a sheet with some data, and I've used a table with the solution I want.

I would like to SUMIFS each column and use the mapping in column A, how ever the range is dynamic from month to month, so for example for this month for Sales I have 56 rows of data that I would like to SUM, from Row 4 (Sales) to Row 59 (Total Sales), but the next month it might be 80 rows from (Sales) to (Total Sales), so I would like SUMIFS for each column everything between Sales and Total Sales.

I would like to do the same for Marketing and Analytics below since these ranges vary from month to month.

I've attached a solution with the fixed rows and columns, but this isn't dynamic and needs to be redone each month, which I would love to avoid.

Many thanks

Try:

Formula:

Please Login or Register  to view this content.

copied down and across.

I would advocate NOT using INDIRECT as it is a volatile function and should be avoided if possible. See the alternative in post #4 which is also shorter.


If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

Set up 3 Named Ranges (Sales, Marketing & Analytics... CTRL-F3 to view/edit). This is sales

=INDEX(Sheet1!$A:$G,MATCH(Sheet1!$I$4,Sheet1!$B:$B,0),):INDEX(Sheet1!$A:$G,MATCH("Total "&Sheet1!$I$4,Sheet1!$B:$B,0),)

Then use variants of this formula, copied across and dnown:

=SUMPRODUCT(--(INDEX(Sales,,1)=$I5)*(INDEX(Sales,,2+COLUMNS($J5:J5))))

You're welcome.

---

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please select "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.