• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 104
  • Last Modified:

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
0
happy 1001
Asked:
happy 1001
2 Solutions
 
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
1
 
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
1
 
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now