Solved

Microsoft Excel Auto Total Calculation

Posted on 2014-04-28
6
434 Views
Last Modified: 2014-04-29
Hi,

I am wondering if there is a way to create a sum total in Microsoft Excel for a cumulative range. I have attached a version of the table so that you can see what I mean.

So right now, the total column on the left is set to sum total the row, which is great. But each consecutive row is actually cumulative, not individual. So right now, the first row has a total of $500 and the third row has a total of $800, because the $500 from C4 is the same $500 from C2, not an additional $500.

All I want to do is make the last total (where the 2 totals meet - in this example B7) show the total based on which cell has value. So if there is value in B2, the total in B7 will show that. But if there is a new value in B3, then the total in B7 should then reflect B3 instead of B2. I cannot do a simple sum because this would reflect more (as all of the totals are cumulative).

I know that I can select the B7 total reflect the value by entering "=(cell)", however I am working with a large quantity of figures and worksheets, so I would prefer that Excel do this automatically. Especially if I have to go back and edit one figure in the table at a later date.

Thank you!
For-EE---Cumulative-Total.xlsx
0
Comment
Question by:Kristina
6 Comments
 
LVL 5

Expert Comment

by:dani gammon
ID: 40028567
Hi Kristina,

I'm sorry if I didn't understand your question correctly.  Are you trying to sum Row B?  so that if you update Row C-Row I, it takes those numbers into account?  

I would use: =sum(b2:b6)

Do you want your total to reflect a sum, a count, or just a cell if it meets certain criteria?  And, if so, what is the criteria?
0
 
LVL 8

Accepted Solution

by:
itjockey earned 250 total points
ID: 40028709
I will suggest use =MAX(B2:B6) if range is cumulative.

Thanks
0
 
LVL 32

Assisted Solution

by:Rob Henson
Rob Henson earned 250 total points
ID: 40029006
Along similar lines to ITJockey, but allows for cumualtive total to go down rather than just assuming highest is latest using DMAX function:

In B8 use
=INDEX($A$1:$B$7,MATCH(DMAX($A$1:$B$7,$A$1,$L$1:$L$2),$A$1:$A$7,0),2)

In L1:L2 put the following:
L1 =B1  will return "Total"
L2 <>0

This says look at the table and return the value from Total column against the latest date where that value does not equal zero.

Thanks
Rob H
0
 
LVL 32

Expert Comment

by:Rob Henson
ID: 40029010
Sorry, that should be in B7 and the ranges in the formula should stop at 6 rather than 7.

In the version I downloaded I had left your original in B7 and had put mine in B8

Thanks
Rob
0
 

Author Closing Comment

by:Kristina
ID: 40029972
Thank you for the help!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
When you upgrade from Windows 8 to 8.1 or to Windows 10 or if you are like me you are on the Insider Program you may find yourself with many 450MB recovery partitions.  With a traditional disk that may not be a problem but with relatively smaller SS…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

895 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

20 Experts available now in Live!

Get 1:1 Help Now