Solved

Excel - Sum Formula

Posted on 2016-08-02
3
69 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 48

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 31

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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Excel formula 6 22
onOpen. check dates in column, colour background 6 20
VBA in SharePoint 3 18
MIN, using ARRAY 4 16
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

705 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now