Dynamically choose column to sum/countif from a wider range?

JD_PowerSouth

I am working on some budget tracking. I have a source sheet with each expense account and how much is budgeted for each month (Jan-Dec) and it goes back years. I want to display the info on a graph that show Jan-Dec and the sum of all expense account budgeted amounts in each cell. Right now in my metric sheet I have the following formula:

Jan Budgeted Amount=SUMIFS({ExpenseAccountsBudgetMaster_JAN}, {ExpenseAccountsBudgetMaster_Year}, Year@row)

I have to duplicate this for Feb Budget and so on, changing the column reference to {ExpenseAccountsBudgetMaster_FEB} etc.

Is there a way to choose the whole range and have an additional condition to choose column referencing the month number? So the formula in the cell could be a column formula?

I want it to function something like this, but I know Sumifs won't do this:

=Sumifs({ExpenseAccountsBudgetMaster_JAN-DEC},Column number,[Mo#]@row, {ExpenseAccountsBudgetMaster_Year}, Year@row)

If I can get this to work, then I'll be able to use this to do a rolling 12 months analysis. I already have dynamic formulas to populate the month and year, but don't know how to pull the budget information in.

Thanks for the help!

Soumitra Bhowmick

A formula like this can help you to some extent, but it is not dynamic, just using IF to switch between ranges

=SUMIFS(IF(Month@row = "Jan", Jan$1:Jan$12, IF(Month@row = "Feb", Feb$1:Feb$12, IF(Month@row = "Mar", Mar$2:Mar$12))), Year$1:Year$12, [Year']@row)

Is it possible to keep the data in Unpivoted manner.
Currently Your data is in the structure I assume

Can it be kept like this?

This would solve all your issues. Moreover, with this format, you can get data from users using a form as well, they don't have to come to the sheet to enter data.

JD_PowerSouth

Thanks for taking the time to respond! I have considered displaying data like you mentioned in your second screenshot, but it would be incredibly expansive as our budget has wide range of categories. The sheet would expand from a few hundred rows to thousands. I was hoping there was an alternate solution.

JD_PowerSouth

Is there a way to do this with sumifs combined with index/collect?