Avatar of marku24
marku24Flag for United States of America asked on

Referencing the data value in Excel SUMIFs function dynamically

I am using the the below SUMIF formula:

=SUMIFS('Budget Allocation '!H:H,'Budget Allocation '!A:A,'Proposed Birds Eye Grid_FED'!$B8,'Budget Allocation '!D:D,'Proposed Birds Eye Grid_FED'!$AS$4)/Menu!$E$6

The value string i am pulling is in column H on a sheet called Budget Allocations.  I would like to make that column reference dynamic and pull from a looked up value in another sheet with the cell reference:


I cannot get the concatenation correct.  It should look something like:

=SUMIFS('Budget Allocation '! & "Menu!B11" & ":" & "Menu!B11",........

Any help?

Microsoft ExcelMicrosoft Office

Avatar of undefined
Last Comment
Rob Henson

8/22/2022 - Mon

You can use the Indirect function like this:
=SUMIFS(INDIRECT("'Budget Allocation'!"&Menu!B11),'Budget Allocation'!A:A,'Proposed Birds Eye Grid_FED'!$B8,'Budget Allocation'!D:D,'Proposed Birds Eye Grid_FED'!$AS$4)/Menu!$E$6

Open in new window


Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question

@Brad: Good catch. The formula worked for me because I have H:H in Menu!B11. Looking at the question again I can see they were looking for the ability to just enter the column name and to have the formula to create the range!

I had to test your formula to make sure that it wouldn't work with INDIRECT("'Budget Allocation'!"&Menu!B11) and just the letter "H" in cell B11. I suspected there might be a problem, but don't use INDIRECT often enough to know for sure. But sure enough, I got #REF! when I tried it.

I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Rob Henson

You can also use the OFFSET function to create a range with which to use the SUMIFS function:

=SUMIFS(OFFSET('Budget Allocation '!$A$1,0,MATCH(Menu!$B$11,{"A","B","C","D","E","F","G","H","I","J","K","L"},0),1048576,1),'Budget Allocation '!A:A,'Proposed Birds Eye Grid_FED'!$B8,'Budget Allocation '!D:D,'Proposed Birds Eye Grid_FED'!$AS$4)/Menu!$E$6

Change the A to L options to match the range of columns that you will be using; I have assumed it is for a monthly report so have used 12 columns.

If my assumption is correct about being monthly, you might be able to do away with the reference to B11 containing the column letter. I assume your Budget data has column headers of dates or month names and your summary that you are pulling the data into also has a date/month. You may be able to use that to match the column in the Budget data headers.

Couple of other points:
Which sheet is the formula being used on? Is it on one of the sheets referenced in the formula? If so, you don't need the sheet reference for that sheet in the formula, you can just use the cell reference. This then makes the formula somewhat shorter.

Do you realise that Budget Allocation sheet name has a trailing space?