# Can I reference a cell to call for a sum_range?

1. ## Can I reference a cell to call for a sum_range?

I apologize, my title might not make sense. I will try to explain better what I am trying to do

Here is a formula I am working with, it works fine as is:

=SUMIFS('Acreage Values'!\$X\$3:\$X\$3565,'Acreage Values'!\$J\$3:\$J\$3565,"WES",'Acreage Values'!\$E\$3:\$E\$3565,\$B16,'Acreage Values'!\$BI\$3:\$BI\$3565,"<>Neither")

I would like to modify it and make it easier to use. This is the sum_range portion: 'Acreage Values'!\$X\$3:\$X\$3565. I want to be able to call a cell that will change the 'X' in the sum_range to another column. For instance, if I want 'X' to be 'BC' instead. In cell E5 of the current sheet, I want to be able to type 'BC'. I then want a formula in my sum_range to grab whatever is in cell E5 and replace X with the contents of E5, in this case, BC. I just cannot figure out how to change the sum_range to make this happen.

2. ## Re: Can I reference a cell to call for a sum_range?

Try (untested)

=SUMIFS(INDIRECT("'Acreage Values'!\$"&E5&"\$3:\$"&E5&"\$3565"),'Acreage Values'!\$J\$3:\$J\$3565,"WES",'Acreage Values'!\$E\$3:\$E\$3565,\$B16,'Acreage Values'!\$BI\$3:\$BI\$3565,"<>Neither")

3. ## Re: Can I reference a cell to call for a sum_range?

I am going to have take a minute and learn how the indirect function works....but that worked! Thanks Ace, very helpful

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1