Solved

Excel - Sum Formula

Posted on 2016-08-02
3
76 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 49

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 32

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

777 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