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

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!

Find more posts tagged with

Formulas

columns

Dynamic

INDEX

summarize

Accepted answers

All comments

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?

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.