» Creating a Summing Formula that Automatically Adjusts to a Dynamic Range
CATEGORY - Excel Summing
VERSION - All Microsoft Excel Versions
Problem: 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:
- Microsoft Office XP Step-By-Step (With CD-ROM)
- Rich Dad, Poor Dad: What the Rich Teach Their Kids About Money--That the Poor and Middle Class Do Not!
- Fish! A Remarkable Way to Boost Morale and Improve Results
- Microsoft Excel VBA Programming for the Absolute Beginner
- The Analysis and Use of Financial Statements
- Excel Charts
No comments have been submitted.
