how to combine sumif with index / match to make formula dynamic for updates

Hi guys

Previously in my models i've found that sometimes when i had to make updates it would be quite manual when formulas work across tabs, having to edit rows etc.

I was wondering if someone can provide some suggestion on how i can improve it.

I've attached an example work book.

Currently I have summary linking to the monthly tab.

If i insert some new rows the formulas still link so its not an issue but for example if i add a bunch of extra detail in the monthly tab i would need to manually link them all in the summary.

Ideally id have some index match (i assume?) formula which goes "this is the line item we want to sumif based on the year". The assumption of course is that the Line item (in this example Total Revenue, Total Costs) are same on both tabs.

Many thanks for any help / advice / suggestions

See if this is what you are trying to achieve...

On Summary Tab
In C4

Please Login or Register  to view this content.

and then copy it across and down.

Originally Posted by sktneer

See if this is what you are trying to achieve...

On Summary Tab
In C4

Please Login or Register  to view this content.

and then copy it across and down.

Thank you very much. This works. I was wondering though - why is the initial IF function required? This formula also seems to work on:

Please Login or Register  to view this content.

Many thanks

EDIT: also, i guess id still need to edit the Z1000 based on how big the workbook gets.

Is there someway of perhaps combining it with an "end function" (if one exists) which identifies what the last active cell in workbook is? e.g. sort of how we can get to it using CTRL+END otherwise this would need to be manually adjusted as well i guess.

Could potentially just have it index the whole tab but i assume that would slow down the file / make it larger?

Yes, the formula would work without the outer IF formula. I added this as I used a single formula which I placed in C4 and then dragged it down and across. If you drag the formula in C4 down to C5 and since B5 is empty it will return an error and to avoid that I added that extra IF condition so that same formula in C4 can be dragged across and down.

But if you don't need that extra IF formula, you can have a formula without it.

If that takes care of your original question, please mark your thread as Solved by selecting Thread Tools (just above your first post) --> Mark thread as solved.

You may also say thanks to those who have put their time and efforts to help you in this forum by clicking the Add Reputation link under their posts, another way to say thanks.

You may use this as well...

Please Login or Register  to view this content.

Index works differently and stops evaluating once the Match function finds the first match so it is safe to use whole column, row or all the rows reference in it. So you should not have any performance issue with it.

Thanks for the rep!