Excel - Sum Formula

Hi Experts

I am trying to write the SUM Formula inside the excel sheet in such a manner that I do not have to manually enter the Cell Range for which the SUM has to be done. Instead I have a column in which cell range are given already, I want the formula to pick up the cell range from there itself.

I am not being able to figure out, how to write this formula. Can someone please suggest a solution ?

And also please mention if I can reuse this solution multiple times in the same formula or not, in case I need to pick up 2 or more cell ranges inside one single Sum Formula.

I have attached the Excel File having the data and I have explained the formula requirements inside it, with comments.

SUM-Formula.xlsx

I am using the following software versions -
Microsoft SQL Server Management Studio version-  12.0.2000.8,
Microsoft Office 2016 x64
and Windows 7 x64

Thanks for any help

Thanks
happy 1001Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rgonzo1971Commented:
Hi,

if you omit the parentheses in Col B you could use

=SUM(INDIRECT(B10))

Open in new window

or use ( with or without () )
=SUM(INDIRECT(SUBSTITUTE(SUBSTITUTE(B10,")",""),"(","")))

Open in new window

or with ()
=SUM(INDIRECT(MID(B16,2,LEN(B16)-2)))

Open in new window

Regards

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rob HensonFinance AnalystCommented:
Does your real data have some other identifying method for the rows to include?

Just thinking you might be able to use the SUBTOTAL wizard which analyses a column of data and inserts a row where the identifier in that column changes and adds a SUBTOTAL for the rows above.

For example in your sample, if your data had an ID in column A and rows 9 and 10 had an ID of 1 and rows 11 to 16 had an ID of 2. You could tell the SUBTOTAL wizard to look at Column A and for each change in ID to add a subtotal to column C, it would do the ranges automatically. The subtotal would be added below the last cell of the range in column C.

Alternatively, if the data doesn't already have an ID you could add one. If the data comes with the range grouping in column B you could use the following in column A, starting in A9:

=IF(B8<>"",A8+1,A8)

If you don't want rows added for the totals then you can still do the above adding of reference and then use SUMIF function, in E9 and copied down to all cells in column E and not just those with the range detail.

=IF(B9="","", SUMIF(A:A,A9,C:C))

With the last suggestion when the data updates in columns B & C, a double click of the bottom right corner of the last cell with a formula in column A would fill down the reference formula and then the same in column E would fill down the SUM formula.

Thanks
Rob H
happy 1001Author Commented:
@Rgonzo1971, Thank you so much, the very first formula seems to work fine in this case. I will ask for help again, if I face difficulty in creating complex formulas using this method of INDIRECT, where I will need to refer to many different cell ranges within one single formula.


@Rob Henson, thanks a lot for explaining different approach. It is always useful to know various ways of doing the same thing.

Thanks to both of you for your help.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.