Sumifs using a cell reference

[rickycr]

I am going to try and explain what i need to accomplish. I am setting up a budget that gets rolled forward every year. every year I need to add an actual amount to a column of data. I also have to update sheets to show what the actual was for the year. every year, an old year comes off, and a new one comes on. I created a table named TBData which shows actual data:

account # description 2011 2012 2013 actual to 10/31/14
1000 expense1 5,000 3,000 3,600 4,500
(the real table (TBData) has about 120 accounts)
next year, i will be adding a "2014 column between 2013 and actual to 10/31/14

on my "analysis" tab, i show the following:
account # description 2012 budget 2012 actual 2013 budget 2013 actual 2014 budget actual to 10/31/14
next year, this tab will look like this:
account # description 2013 budget 2013 actual 2014 budget 2014 actual actual to 10/31/2015

on the "analysis" tab, i am trying to write a sumifs formula that uses another cell as a reference as follows:
=sumifs(TBData[2012],TBData[account],A1)
What my goal is to accomplish, is to change the 2012 in the formula to 2013. in the past, it was a pain in the rump as the formula had to be recopied down. I want to reference the 2012 in this formula to another cell and then when i have to change the year, i just enter it in the referenced cell. does anyone have any suggestions?

[FDibbins]

Hi, welcome to the forum

I think you need to look at using INDIRECT() for this. Lets say your year is in B1, then your formula would look something like this...

=sumifs(INDIRECT("TBData[:&B1&"]"),TBData[account],A1)
(Untested)

[rickycr]

Hi, welcome to the forum

I think you need to look at using INDIRECT() for this. Lets say your year is in B1, then your formula would look something like this...

=sumifs(INDIRECT("TBData[:&B1&"]"),TBData[account],A1)
(Untested)

I saw indirect, but was unclear how to use it. Anyway, i tried it as follows:
=ROUND(-SUMIFS(INDIRECT("TBData[:&E4&"]"),TBData[Account],A9),0) but get an error that the "formula contains an error." my actual cell for the year is cell E4 in this case. am i missing something?
Thanks again for the reply FDibbins!

[FDibbins]

Look closely at where I had my ""...

=sumifs(INDIRECT("TBData[:&B1&"]"),TBData[account],A1)
(-SUMIFS(INDIRECT("TBData[:&E4&"]")

The way it works, is that INDIRECT() allows you to convert text into something that excel will recognise as a reference/range. Keeping that in mind, all text in a formula needs to be surrounded by ""

So what we need to do, is ID what will be text and what will be a cell ref. (to keep it simple, I am leaving out the last 2 parts of teh SUMIFS().

the 1st part TBData[2012] is where we want to replace the hard-coded year with a year reference cell.
To do that, we need to break it into 3 parts...TBData[...2012...]
the 1st part will become text, so "TBData["
the 2nd part will be the cell ref $E$4 (I absoluted the reference in case you copy it)
the 3rd part will also become text "]"

sooooooo (again, untested)...
=ROUND(-SUMIFS(INDIRECT("TBData["&E4&"]"),TBData[Account],A9),0)

I would test this without the other bits, to make sure it is working properly, maybe just...

=INDIRECT("TBData["&E4&"]") and see what it returns

Another thing that is confusing the issue is the structured table referencing. You can get around this my either converting back to range, or by just typing in the range refs manually

If you still have a problem, upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

[rickycr]

FDibbins:

tried directly entering the formula as you had it, kept getting the formula contains an error. i then entered
=INDIRECT("TBData["&E4&"]". the cell returned a number (no idea if its right) i then worked backwards by entering the sumifs before it, and Loe and behold... it WORKS!!!!!!!!!! changed the year to 2013 to verify its working, and viola, it DOES!!!!!!!

Thank you so much for your time. Now its just going take some time to copy the formula down, but the future will make my life so much easier!

Thanks again!!!!
(I'm just a little happy, in case you couldn't tell!)

[FDibbins]

=INDIRECT("TBData["&E4&"]". the cell returned a number (no idea if its right)

That will be a mumber from (probably) the top left corner of that range.

Breaking it down into smaller chunks and working from inside outwards - adding on extra functions - is a great way to make sure each part works as needed

Just make sure that you use $E$4 in there, and not just E4, this will anchor/absolute that reference so it wont change when copied down

Im glad we got this resolved for you, and I look forward to helping you again soon

If this answered your question, please take a moment to mark the thread as "solved" - it helps keep things neat and tidy lol, and consider adding the reputation to those that helped (see points 2 & 3 below my 1st post to you)