Can't find a function that works like sumifs but finds the summary column via a criteria.

Copper Contributor

I have a database I'm building that I need to reference to an old form, however, I can't seem to find a function that does what I need it to do.

One of my favorite formulas is the SUMIFS, which I can use to pull the total dollar amounts of items based off of multiple criteria. I use around 5 criteria in this spreadsheet to find the costs I need. What I'm having issues with is the costs are broken out by month via columns. Using SUMIFS, I can't tell it what summary range or column to select via a criteria. Here's the database.

jmart320_0-1680717751556.png

And here's the old form I'm trying to put SUMIFS in to find the costs in the database.

jmart320_1-1680717930753.png

 

I'm able to get the SUMIFS to pull the approved budget from the database because I simply tell the formula that that sum range is the amended budget column, but for current month expenses, I want it to select a column based off of the criteria in the month block in cell E4. I cannot do that with SUMIFS, and I can't seem to find a formula that does that.

 

Currently I'm looking into creating my own formula to remedy this but that's a new concept to me and will take me awhile to figure out. The end goal would be to change the month and it would search the respective month column.

 

Thanks for any input!

1 Reply

@jmart320 

You could use a double XLOOKUP like this:

 

=XLOOKUP(F2&F3,dept&code,XLOOKUP(F1,months,data))

My example only uses two criteria but you get the idea.