Solved

Excel - Sum Formula

Posted on 2016-08-02
3
81 Views
Last Modified: 2016-08-02
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
Comment
Question by:happy 1001
3 Comments
 
LVL 50

Accepted Solution

by:
Rgonzo1971 earned 400 total points
ID: 41738874
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
 
LVL 33

Assisted Solution

by:Rob Henson
Rob Henson earned 100 total points
ID: 41739321
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
 

Author Comment

by:happy 1001
ID: 41739367
@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

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
EXCEL Name Matching 13 47
Export Outlook 'ghost contacts' 11 38
any combination of this numbers 9 33
VBA code to edit a column based on if statements 25 23
In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question