Sumifs formula where arrays for the different criteria expands with a dynamic named range

[Trille]

Hi!

This might be a stupid question, but I've been at it for a while and I can't seem to wrap my head around the solution.. Right now, I have the following formula:

=SUMIFS(OFFSET(Name;;;ROWS(Name)-1;1);M19:M20;"x";F19:F20;"y")

So, I have a named range ("Name") in column K that starts at row 19 and expands downwards when creating new rows. What I would like to achieve is for the criteria ranges (in red) in my sumifs formula to mirror the number of rows in the named range. Can I do another offset from the column K (+ 2 columns for the first criteria and -5 for the second) or should I use some other function? This represents a small section of a large worksheet, so if possible, I would like to avoid creating dynamic ranges for all criteria.

I hope that my question isn't too vague.. appreciate any input I can get!

/Tristan

 

[Fluff]

Why not just use something like

=SUMIFS(K:19:K2000;M19:M2000;"x";F19:F2000;"y")

Change the 2000 to any row well below your data.

 

[Trille]

Thanks for answering Fluff!

I can't because the worksheet is built like different budget sections on top of each other, and each section has a macro that creates new rows to keep it above the next header.. Why make it simple when you can make it difficult, right?

Anyway, I solved the problem.. It wasn't more difficult that to just copy the first OFFSET part and adjust the columns... Sometimes a cup of coffee and some fresh air can do wonders! Anyway, for order's sake, this is what I ended up with:

=SUMIFS(OFFSET(Name;;;ROWS(Name)-1;1);OFFSET(Name;;2;ROWS(Name)-1;1);"x";OFFSET(Name;;-5;ROWS(Name)-1;1);"y")

Not the prettiest, but it works

Thanks for your time!

 

[Fluff]

Glad you sorted it & thanks for the feedback.