» Creating a Summing Formula that Automatically Adjusts to a Dynamic Range
CATEGORY - Excel Summing
VERSION - All Microsoft Excel Versions
List1 (column B) initially consisted of the numbers from cell B2 to cell B7.
In order to calculate their sum the following formula was entered in cell B8:
=SUM(B2:B7)
After performing the above calculation, three more numbers were added to the list, and the formula had to be altered to include those numbers as well.
We want to avoid having to continually make this update by creating a formula that will automatically sum the entire range whenever new values are added.
Solution:
Use the SUM, IF, COUNT, and OFFSET functions as shown in the following formula:
=SUM(B2:B7,(IF(COUNT(B9:B990)>0,OFFSET(B9,0,0,COUNT(B9:B990)),0)))
Book Store:
Recommended Books:
- Business Analysis with Microsoft Excel (2nd Edition)
- The Interpretation of Financial Statements
- The 22 Immutable Laws of Branding
- The One Page Business Plan: Start With a Vision, Build a Company!
- Windows XP for Dummies
- Lower Your Taxes - Big Time! : Wealth-Building, Tax Reduction Secrets from an IRS Insider
Re: Creating a Summing Formula that Automatically Adjusts to a Dynamic Range
JBeaucaire
Seems like a simpler answers would be:1) Put the SUM formula at the top of range then sum all the cells below in an all-inclusive way, perhaps in B1:
[B]=SUM(B2:B10000)[/B]
2) Put the sum in a different column and you can use:
[B]=SUM(B:B)[/B]
3) When I have [B]=SUM(B2:B7)[/B] in cell B8 and I insert a blank cell at B8 (causing the sum formula to drop to B9), then enter a value in the new empty cell at B8, my Excel updates the formula in B9 to [B]=SUM(B2:B9)[/B]...doesn't yours?
Re: Creating a Summing Formula that Automatically Adjusts to a Dynamic Range
arthurbr
Isn't this thread stating the obvious? This problem has been asked and answered a zillion times
Re: Creating a Summing Formula that Automatically Adjusts to a Dynamic Range
JBeaucaire
[QUOTE=arthurbr;2145680]Isn't this thread stating the obvious? This problem has been asked and answered a zillion times[/QUOTE]
I don't know if the thread itself does that, but my post most certainly does.
Use the example in the attachment.
Steps:-
1. Highlight column A and name it as "No".
2. Key in the formula in cell B7, =SUM(OFFSET(B1,0,0,COUNTA(No),2))
You should be able to sum it whenever you added a new row, provided that the sequence no is updated as well.
Give it a try.